A FileMaker Date Fix Calculation

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Date fields are designed to store date based information. Because of this, the entered data needs to contain month, day and year data and this data needs to be separated by a common text character. The most common format in the United States is month, day and then year ( 02/28/2004 for example ). FileMaker will not allow you to type in date related data it cannot understand as a date. You cannot enter in “Next Wednesday” in a date field. FileMaker will bark at you, via a dialog box, when you violate date based formatting that it understands.

However, FileMaker does have a few holes in it’s protection of the format of a date field. In fact, I wouldn’t call them holes as much as blindspots. Bad date data ( for a lack of a better term ) can be introduced into a FileMaker date field in at least 3 different ways. There may even be more ways but these are the ones that come to mind.

Importing Data From Another Source Into The Date Field
(although there is a check box to prevent this on the import dialog box, it’s final results can be less than ideal.

Converting A Text Field To A Date Field
(although FileMaker does warn you via a dialog box that this might not be a good idea)

Using The Set Field Script Step

Each of the above will allow you to place data into a date field without any date format checking.

So your calculations or searches that rely on this information may be incorrect or fail completely. Here are a few incorrectly formatted dates that could be imported into a date field and a function that would help you fix them. We will be using text functions to slice and dice the string of text and then put them together in the proper format. We also used a Left to zero calculation to put in a note where each calculation for the month, day and year start.

If you have a found set of the incorrectly formatted date records, you could use the replace command to fix the date format, otherwise you could use the SET script step to do them one at a time.

FORMAT: 2001.12.25
TextToDate(
Left("Month", 0) & Middle(Fix Date 1, (Position(Fix Date 1, ".", 1, 1) + 1), Position(Fix Date 1, ".", 1, 2) - Position(Fix Date 1, ".", 1, 1) -1) & "/" &
Left("Day", 0) & Right(Fix Date 1, Length(Fix Date 1) - Position(Fix Date 1, ".", 1, 2)) &
Left("Year", 0) & "/" & Left(Fix Date 1, Position(Fix Date 1, "1", 1, 1)))

FORMAT: 25122001
TextToDate(
Left("Month", 0) & Middle(Fix Date 2, 3, 2) & "/" &
Left("Day", 0) & Left(Fix Date 2, 2)&
Left("Year", 0) & "/" & Right(Fix Date 2, 4))

An example file can be downloaded by clicking (here)

The majority of the text for this example was written when FileMaker 6 was the latest version of FileMaker. That of course is no longer the case. So many more and elegant ways to cleanup a date field are likely available. If you have FileMaker Advanced, you might give serious consideration towards a custom function. Here you likely find half a dozen custom functions for this particular need.

Check out the custom function library at

http://www.briandunning.com/filemaker-custom-functions/recentlist.php ,

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.