FileMaker Pro 9: The Missing Manual

Date and Time Calculations

FileMaker can be a little esoteric about dates and times. If you don’t understand how they work, you can end up wasting a lot of time trying to do things that FileMaker can easily do for you. For example, you may need to know the first day of the month following the date an invoice is due. You can spend ages writing a calculation that takes leap years and the different number of days in each month into account. You’d be sweaty, tired, and proud when you were done—six hours after you started. But if you know how dates work in FileMaker, you can just type this single line:

	Date ( Month ( Invoice Due Date ) + 1 ; 1 ; Year ( Invoice Due Date ) )

How FileMaker Looks at Time

Before you start writing date and time calculations, you need to know how FileMaker actually keeps track of dates and times. FileMaker internally stores any date or time value as a single number that makes sense to it. Then, when it needs to display a date or time, it converts the number to a value people recognize as a date or time, like “11/7/2007” or “10:23 AM.” As with other numbers that it stores one way and displays another, FileMaker does the math on the stored value, and then converts it for your convenience. Here’s how FileMaker keeps track of time:

• It stores a date as the number of days since the beginning of the year 1 A. D.

• It stores a time as the number of seconds since midnight.

• It stores a timestamp as the number of seconds since midnight at the beginning of the year 1 A. D. (a ...