Date - Number vs. Text

[Utterly Bamboozled]

Problem

MID(CELL("filename",C2),(FIND("]",CELL("filename",C2))+1),50) returns name of Sheet 1 = 'P&L Sep-10'

Then use right() to retrieve 'Sep-10' from this name. This is in a text format, and as far as I know cannot be changed to a date format.

My problem lies in using this 'Sep-10' cell in tandem with other dates in another sheet.

Specifically I need to use > & < in the calcs, which don't work when the dates are formatted as text. I.e. =if(A1<B1) returns FALSE where A1=Jan-10 and B1=Feb-10<B1) B1="Feb-10<B1)" and A1="Jan-10" where FALSE returns>

Any assistance would be appreciated!

 

[xenou]

You can drop that into a DATEVALUE() function:
DATEVALUE("Sep-10")
DATEVALUE(RIGHT(XXX,YYY))

I don't know what Excel does with a missing day though. Sep 10 is a duration of 30 days, not a single date. You may want to create a real date:
DATEVALUE("01" & "-Sep-" & "2010")

Then you can compare one month to another - they will all have day one by default and can be compared this way. The Sep and the 10 will come from your cell values, and the 01 you supply. I like 4-digit years so I will add "20" to the year value "10". I guess this formula will only be good for another 85 years or so. Too bad.

 

[Utterly Bamboozled]

All good guys - have just discovered the Datevalue() function. Very handy.

Thanks anyway.

 

[Utterly Bamboozled]

Thanks Xenou! We must have posted simultaneously. Do you know whether Datevalue() is compatible with Excel '03?

Cheers

 

[xenou]

Thanks Xenou! We must have posted simultaneously. Do you know whether Datevalue() is compatible with Excel '03?

Cheers

Yes, I think we did. DateValue() works fine with XL 2003.
Cheers,
ξ