date reference

[excelFan]

hi all,
can a single cell accomodate TWO different DATES, and yet refer to them
in formulas?
i tried having in A1 [from 07/04/01 to 10/15/03] and by using
right(left(a1,13),8) to extract data starting from 07/04/01, and right(a1,8)
also to refer to end of period selected at 10/15/03. always i get a zero
anwser or error.
thanks for any help

 

[Frank Kabel]

Hi
what is the exact value in cell A1 and what are your exact formulas?

 

[JulieD]

Hi

the problem is once you incorporate text with dates, excel no longer
recognises that they're dates, however to extract the start date use
=DATEVALUE(MID(A1,6,8))
and the end date use
=DATEVALUE(RIGHT(A1,8))

you can then format them however you want (format / cells / date)
Cheers
JulieD

 

[excelFan]

A1=from 07/04/01 to 10/15/03
where "kDt" is the name of column where dates are, and
"kAm" is the name of field of amounts to be summed
i added 0.25 in find function to avoid having error ic case the "." do not
exist
my formula is
=MID(SUMPRODUCT(--(kDt>=RIGHT(LEFT(A1,13),8)),--(kDt<=RIGHT(A1,8)),kAm),1,FIND(".",SUMPRODUCT(--(kDt>=RIGHT(LEFT(A1,13),8)),--(kDt<=RIGHT(A1,8)),kAm)+0.25)-1)

the result is 0

Hi
what is the exact value in cell A1 and what are your exact formulas?

--
Regards
Frank Kabel
Frankfurt, Germany

hi all,
can a single cell accomodate TWO different DATES, and yet refer to them
in formulas?
i tried having in A1 [from 07/04/01 to 10/15/03] and by using
right(left(a1,13),8) to extract data starting from 07/04/01, and right(a1,8)
also to refer to end of period selected at 10/15/03. always i get a zero
anwser or error.
thanks for any help

 

[Frank Kabel]

Hi
not really sure what your expected result would be. what are you trying
to achieve with this formula

--
Regards
Frank Kabel
Frankfurt, Germany

A1=from 07/04/01 to 10/15/03
where "kDt" is the name of column where dates are, and
"kAm" is the name of field of amounts to be summed
i added 0.25 in find function to avoid having error ic case the "." do not
exist
my formula is :
=MID(SUMPRODUCT(--(kDt>=RIGHT(LEFT(A1,13),8)),--(kDt<=RIGHT(A1,8)),kAm)
,1,FIND(".",SUMPRODUCT(--(kDt>=RIGHT(LEFT(A1,13),8)),--(kDt<=RIGHT(A1,8
)),kAm)+0.25)-1)

the result is 0

Hi
what is the exact value in cell A1 and what are your exact formulas?

--
Regards
Frank Kabel
Frankfurt, Germany

hi all,
can a single cell accomodate TWO different DATES, and yet

refer to
them

in formulas?
i tried having in A1 [from 07/04/01 to 10/15/03] and by using
right(left(a1,13),8) to extract data starting from 07/04/01, and right(a1,8)
also to refer to end of period selected at 10/15/03. always i

get a
zero

anwser or error.
thanks for any help

 

[excelFan]

many thanks JulieD
that does it and works fine

Hi

the problem is once you incorporate text with dates, excel no longer
recognises that they're dates, however to extract the start date use
=DATEVALUE(MID(A1,6,8))
and the end date use
=DATEVALUE(RIGHT(A1,8))

you can then format them however you want (format / cells / date)
Cheers
JulieD

hi all,
can a single cell accomodate TWO different DATES, and yet refer to them
in formulas?
i tried having in A1 [from 07/04/01 to 10/15/03] and by using
right(left(a1,13),8) to extract data starting from 07/04/01, and
right(a1,8)
also to refer to end of period selected at 10/15/03. always i get a zero
anwser or error.
thanks for any help

 

[excelFan]

thanks Frank
i'm trying simply to add values between two dates!

Hi
not really sure what your expected result would be. what are you trying
to achieve with this formula

--
Regards
Frank Kabel
Frankfurt, Germany

A1=from 07/04/01 to 10/15/03
where "kDt" is the name of column where dates are, and
"kAm" is the name of field of amounts to be summed
i added 0.25 in find function to avoid having error ic case the "." do not
exist
my formula is :
=MID(SUMPRODUCT(--(kDt>=RIGHT(LEFT(A1,13),8)),--(kDt<=RIGHT(A1,8)),kAm)
,1,FIND(".",SUMPRODUCT(--(kDt>=RIGHT(LEFT(A1,13),8)),--(kDt<=RIGHT(A1,8
)),kAm)+0.25)-1)

the result is 0

Hi
what is the exact value in cell A1 and what are your exact formulas?

--
Regards
Frank Kabel
Frankfurt, Germany

hi all,
can a single cell accomodate TWO different DATES, and yet refer to
them
in formulas?
i tried having in A1 [from 07/04/01 to 10/15/03] and by using
right(left(a1,13),8) to extract data starting from 07/04/01, and
right(a1,8)
also to refer to end of period selected at 10/15/03. always i get a
zero
anwser or error.
thanks for any help

 

[Myrna Larson]

And, if the date you are searching for is included in the date range but not
specified exactly, i.e. 8/13/03 is included in the span 7/4/01 to 10/15/03,
you have yet another problem.

Hi

the problem is once you incorporate text with dates, excel no longer
recognises that they're dates, however to extract the start date use
=DATEVALUE(MID(A1,6,8))
and the end date use
=DATEVALUE(RIGHT(A1,8))

you can then format them however you want (format / cells / date)
Cheers
JulieD

hi all,
can a single cell accomodate TWO different DATES, and yet refer to them
in formulas?
i tried having in A1 [from 07/04/01 to 10/15/03] and by using
right(left(a1,13),8) to extract data starting from 07/04/01, and
right(a1,8)
also to refer to end of period selected at 10/15/03. always i get a zero
anwser or error.
thanks for any help

 

[JulieD]

Hi

glad it's solved and thanks for the feedback.

Cheers
JulieD

many thanks JulieD
that does it and works fine

Hi

the problem is once you incorporate text with dates, excel no longer
recognises that they're dates, however to extract the start date use
=DATEVALUE(MID(A1,6,8))
and the end date use
=DATEVALUE(RIGHT(A1,8))

you can then format them however you want (format / cells / date)
Cheers
JulieD

hi all,
can a single cell accomodate TWO different DATES, and yet refer to
them
in formulas?
i tried having in A1 [from 07/04/01 to 10/15/03] and by using
right(left(a1,13),8) to extract data starting from 07/04/01, and
right(a1,8)
also to refer to end of period selected at 10/15/03. always i get a
zero
anwser or error.
thanks for any help