Date Fields: Calculating The Difference Between Dates, Finding Future Dates

FileMaker Pro stores dates internally as number of days since January 1, 0001. ClarisWorks stores them as the number of days since January 1, 1904. For example, the screen may show January 1, 1996, but internally ClarisWorks stores 33603. For the same date, FileMaker Pro stores 728659.

If you have two date fields, and you wish to find the number of days between them, you simply subtract the earlier date from the later date.

In ClarisWorks: DaysDifferent (Calculation, Number result) ='Date2'-'Date1'
In FileMaker Pro: DaysDifferent (Calculation, Number result) = Date2 - Date1

If Date2 was January 1, 1996, and Date1 was January 1, 1995, the formula would return 365.

To find a date in the future, you simply add the number of days in the future to the existing date.

In ClarisWorks: DaysFuture (Calculation, Date result) = 'StartDate'+'SevenDayTrial'
In FileMaker Pro: DaysFuture (Calculation, Date result) = StartDate + SevenDayTrial

If StartDate was January 1, 1996, the result of this calculation would be January 8, 1996.

NOTES:
Since the slash (/) �? a mathematical symbol �? is used to separate the day, month and year in a date, you cannot use that format in a calculation formula. 1/1/1996 in a date field is a date, but in a calculation it means 1 divided by 1 divided by 1996. Which results in .000501002004008016.

To type a date in a formula, you must use either reference date field or use the Date function. For example, January 1, 1996 would be Date(1,1,1996) in FileMaker Pro and Date(1996,1,1) in ClarisWorks.

Be careful to use the full year in a calculation. If you type 1/1/96 into a date field, it would return January 1, 1996. If you type Date(1,1,96) into a calculation in FileMaker Pro, it would return January 1, 96. The year 96. Not 1996. A thousand years earlier!