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

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)

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

What are you talking about? Ignorance is bliss.

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!

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...

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!