Convert Date 03-Apr-04 (text) to 04-04-2004 (Date)

[bkamlin]

I have a field that is text (04-APR-04) that is a date with this format I need to convert it to an actual filemaker date field 04/04/2004.

Any Ideas?

I got the field to APR/04/04 but it is still text and converting the month to numeral date has become a pain.

Version: v5.x

Platform: Windows 2000

[-Queue-]

Create a value list called Months with custom values:

Jan

Feb

Mar

...

Dec

Create a calculation date field:

Date( PatternCount( Left( ValueListItems( Status(CurrentFileName), "Months" ), Position( ValueListItems( Status(CurrentFileName), "Months" ), MiddleWords( text, 2, 1 ), 0, 1 ) ), "

[CyborgSam]

I had this problem importing dates from Access, they didn't take as dates in a date field.

I can't remember exactly how I fixed it, I'm pretty sure I used Replace Contents. You might try replacing theDateField with the calculated result: TextToDate(theDateField).

[spb]

Make an empty date field, do a one-time replace-with-calculation into it. If it works, then delete the original field and use the new date field in the future. If you need to import data in the old format, then you might look at scripting the replace-with-calc as part of your import script (and keep the old text field for importing into). You confuse me with your example, because all three elements, m, d & y, are the same number. 04-04-04 is tough to decode which is which at a glance. But I'll assume mm-dd-yy.

You build your calc one element at a time. The month is the hardest and comes first. If your alpha months are all in the exact three character standard abbreviation matching your example, then you could decode them with a big case statement (I'll call your current field TextDate):

Case(Middle(TextDate, 4, 3) = "JAN", "01,

Middle(TextDate, 4, 3) = "FEB", "02,

Middle(TextDate, 4, 3) = "MAR", "03,

Middle(TextDate, 4, 3) = "APR", "04,

Middle(TextDate, 4, 3) = "MAY", "05,

Middle(TextDate, 4, 3) = "JUN", "06,

Middle(TextDate, 4, 3) = "JUL", "07,

Middle(TextDate, 4, 3) = "AUG", "08,

Middle(TextDate, 4, 3) = "SEP", "09,

Middle(TextDate, 4, 3) = "OCT", "10,

Middle(TextDate, 4, 3) = "NOV", "11,

Middle(TextDate, 4, 3) = "DEC", "12)

This will yield the correct two number characters for the month. Add to that the slashes and numbers from the rest of TextDate:

& "/" & Left (TextDate, 2) & "/" & "20" & Right(TextDate, 2)

I am assuming here that all years will begin with "20" so it is inserted. If not, you can set up another Case statement to test whether the last two digits are from "19" or "20" years.

Tack this on the end of the Case statement, and embed the whole thing in a TextTo date function and you get:

TextToDate(Case(Middle(TextDate, 4, 3) = "JAN", "01,

Middle(TextDate, 4, 3) = "FEB", "02,

Middle(TextDate, 4, 3) = "MAR", "03,

Middle(TextDate, 4, 3) = "APR", "04,

Middle(TextDate, 4, 3) = "MAY", "05,

Middle(TextDate, 4, 3) = "JUN", "06,

Middle(TextDate, 4, 3) = "JUL", "07,

Middle(TextDate, 4, 3) = "AUG", "08,

Middle(TextDate, 4, 3) = "SEP", "09,

Middle(TextDate, 4, 3) = "OCT", "10,

Middle(TextDate, 4, 3) = "NOV", "11,

Middle(TextDate, 4, 3) = "DEC", "12)

& "/" & Left (TextDate, 2) & "/" & "20" & Right(TextDate, 2))

That should do it.

Steve Brown

[bkamlin]

Thanks everyone. A workaround was to convert the fields to dates fields in Excel before the import. But these will help me in the future. The standard date format seems to be an Oracle Export standard. I thought Filemaker would be friendlier with date formats.