Date Conversions

[sheridanla]

I am rather new to FM, or at least have not touched it since v2 or so.. and I am running into a few issues which I have had trouble finding specific answers for.. any help is greatly appreciated.

First one: I am exporting data from a report sent to me, all imports peachy except the date. In the Excel file, it is written as 01-Feb-2006 and I have dates formatted in the DB as 02/01/2006. Previous imports converted the dates to the desired format, but this last one has not. I tried to conduct a search to find those records and delete them (then change the date format in Excel then reimporting them) but it won't let me search by that date format.

I found a script in the FM kbase which would seem to address the problem perfectly, but I am not savvy enough to get it to work. Any help on where to put this script or insert it or tweak it would be awesome.

The fields I am trying to convert are called:

invoice_date

payment_received

The script I found in the kbase is as follows:

Data entered as 02-DEC-96, 02 DEC 96, or 23/JAN/96 are not recognized as dates by FileMaker Pro 4.0 or FileMaker Pro 3.0, and cannot be used to perform finds, sorts, or date calculations. However, you can create a calculation that will convert your data to meaningful dates.

This solution assumes your original date field is of type Text, and that the data has been entered consistently in the format above (DD-Mon-YY, DD Mon YY, or DD/Mon/YY).

To convert your data into usable dates, define the following field:

NewDate (Calculation, Date result)=

TextToDate(Case(

Middle(OriginalDate, 4, 3) = "jan", "1",

Middle(OriginalDate, 4, 3) = "feb", "2",

Middle(OriginalDate, 4, 3) = "mar", "3",

Middle(OriginalDate, 4, 3) = "apr", "4",

Middle(OriginalDate, 4, 3) = "may", "5",

Middle(OriginalDate, 4, 3) = "jun", "6",

Middle(OriginalDate, 4, 3) = "jul", "7",

Middle(OriginalDate, 4, 3) = "aug", "8",

Middle(OriginalDate, 4, 3) = "sep", "9",

Middle(OriginalDate, 4, 3) = "oct", "10",

Middle(OriginalDate, 4, 3) = "nov", "11",

Middle(OriginalDate, 4, 3) = "dec", "12")

& "/" & Left(OriginalDate, 2)

& "/" & Right(OriginalDate, 2))

Use the resulting NewDate field for Finds and Sorts, and in calculations.

***Does this mean I create a NEW field called NewDate? do I do that for both fields? Can I not just convert the existing fields to the new format? and where exactly do I put this script?? ( in other words, what do I do with it?

Thank you in advance.

Edited July 24, 2007 by Guest

[Vaughan]

"I am rather new to FM, or at least have not touched it since v2 or so."

Ok, sure. A couple of things have happened since then that you might need to know about:

1) We landed on the moon.

2) FMP is now relational.

Kidding, just kidding. Really I just couldn't resist.

Ok, first thing is that IMHO don't open text import/export files in Excel, it does awful things to them without asking, like converting dates and stuff. Open the files in a text editor like Notepad if you have to see them, but there really is no need. The easiest way to view a text file is to let FMP convert it into a new database for you. It takes less than a minute.

Once it's been imported into FMP, that's when you can do some of the date converting stuff. Do it all in this intermediate file, then transfer it into the real file. Create calculation fields so the orginal data never gets modified.

TextToDate() leaves you with a text string, which is basically where you started. What you want is to end up with a Date field, which is really flexible. You get this with the Date() function whose syntax is Date( month ; day ; year ).

So change that TextToDate() function to Date() and restructure it to suit the month, day, year syntax. Note that there is no need to insert "/" characters into the function.

But first off, try GetAsDate() on the field and see if FMP is smart enough to convert it right off.