TextToDate

[LaRetta]

I'm sorry I'm a pest right now ... major conversion that I shouldn't be expected to do. I'm NEW, darn it! Anyway, I have a text field (TEXTDATE) with the following format: AUG 31 02 with one space on each side of the day. I need to convert and SET a date field. I tried TextToDate(TextDate) but nothing shows up in the date field.

I tried

LeftWords(TextDate, 1 ) & "/" & MiddleWords( TextDate, 2 , 1 ) & "/" & RightWords( TextDate, 1 ) but that gives me 6/29/0009 -- at least it's closer! When I add TextToDate around it, it's empty again. Anyone that helps me, I'll love forever

[kennedy]

Date(Int(Position("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC", LeftWords(TextDate, 1), 1, 1)/4) + 1,

TextToNum(MiddleWords(TextDate, 2, 1)),

TextToNum(RightWords(TextDate, 1) + 2000)

[LaRetta]

Oooh, that worked PERFECTLY! I NEVER would have be able to come up with that! Thanks HTH (kidding ) -- I didn't know what HTH was ... I thought you wanted me to call you by your initials!?! Ignorance is so embarrasing

[kenneth2k1]

What are you talking about? Ignorance is bliss.

[LaRetta]

Ignorance may be bliss MOST times, but when you're given a deadline to do something you're ignorant about ... and it takes you 100 times longer than it should, well ... that's sucky, Ken!

[CobaltSky]

The principle is correct, but if there could possibly be any dates in the data you are retrieving which are prior to 1/1/2000, then the formula Brian supplied will break - it is not Y19xx compliant!

I suggest that instead, you use:

Date(Position("ABCJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC", LeftWords(TextDate, 1), 1, 1)/3, MiddleWords(TextDate, 2, 1), RightWords(TextDate, 1) + Case(RightWords(TextDate, 1) < 53, 2000, 1900))

Which will work for all dates within a 'radius' of 50 years...

[LaRetta]

Thanks Ray! Luckily this involves 7/1/02 forward. But, I have copied this formula into my Formulas list for future needs. Who knows, management may want prior fiscals converted someday and I want to learn and understand it anway!