by Lorne Walton, <lornew@wimsey.com>
FileMaker's Functions
Date and Time functions
We continue the Beginner's Series with a look at FileMaker Pro v 3's built-in functions for handling dates and time as data. Open issue #21's FUNCTNS.FP3 database bonus file written by ISO staff & contributors.
Subsequent installments will look at:
Aggregate/Summary/Repetition Functions
Financial Functions
Logical Functions
Status Functions
Where to use this?
A date function is a formula used in a calculation that is intended to work with dates. A time function works with times. Fancy that! FileMaker does not prohibit you from applying these functions to non-date/time fields, which is not surprising since FileMaker stores dates and times as numbers.
Functions are employed in field definitions (i.e., when defining a calculation field) and in the ScriptMaker dialog (to create calculations within a script.) There are 12 date functions and 5 time functions.
Date (month, day, year)
Enters the specified date information, independent of the user's computer date & time settings. The function expects a four-digit year between 0001 and 3000, and returns an integer between 1 (representing 1 January, 0001 AD) and 1,095,727 (31 December, 3000.) FileMaker's date-formatting functions convert that integer to something that you recognize as a date. This representation makes it easy to calculate the number of days between two dates. Make "Days" a numeric field, and:
Set Field (Days, Abs (Date2 - Date1))
Years outside the range 1 to 3000 return "?" as the date, and no numeric value.
Date(2,28,1997)
is February 28, 1997 but will be displayed according to the user's system settings. There's little restriction on the values that can be placed in the Month and Day fields. In fact, even
Date(0,25,3001)
is a valid date, equal to Christmas day, in the year 3000. You don't have to fret about adding 30 days to a date in December - FileMaker corrects the year and month for you. Any of these:
Date (12,25,1997) + 30
Date (12,55,1997)
Date (12,55,1997)
Date (1,389,1997)
Date (1,-341,1999)
yields January 24, 1998.
DateToText (date)
This function expects a date as an argument, but in fact, any number will do. You may be surprised by the result:
DateToText (1) is "5/3/0302"
The most important place for DateToText is when searching for a range of dates. Given a date field, "Date", the user who wants to press a button labeled "Find Dates", type in a start date and an end date, and perform the find: create a new layout "Date Finder" with the Date field and two global date fields, "gStartDate" and "gEndDate". Attach this script to your button:
Go to Layout ("Date Finder")
Enter Find Mode (Don't restore, Pause)
Comment [Pause so user can type the dates and press <enter>]]
If (IsEmpty (gStartDate) or IsEmpty (gEndDate) or gStartDate > gEndDate)
Show Message ["Hey, Bozo!"]
Exit Script
Else
Paste Result[Select, "Date", "DateToText(gStartDate) & "..." & DateToText(gEndDate)]
Perform Find
Go to Layout (original layout)
End if
Day (date)
Returns a number, equal to the day of the month. Both of these:
Set Field (MyNumber, Day (Date(12, 25, 1900)))
Set Field (MyNumber, Day (TextToDate ("25/12/1997"))
put the value 25 into the field MyNumber. In the second example it is assumed that the system date format is the international DD/MM/YYYY. In the first example, it doesn't matter, since the date function always works with MM,DD,YYYY.
DayName (date)
Evaluates as a text string, equal to "Sunday" or "Monday", etc.
Set Field (National_Holiday, DayName (Date (7,4,1997))
Puts "Friday" into the text field "National_Holiday."
DayofWeek (date)
Similar to DayName, but produces a number from 1 through 7 as the result.
DayofWeek (Date (7,4,1997))
gives the result 6 since the day is Friday. Sunday = 1, Saturday = 7.
DayofYear (date)
Result is a number between 1 and 366.
DayofYear (12,31,2000)
equals 366. See below for the function "Year" which shows one way of determining if a year is a leap year.
Month (date)
A number from 1 to 12 corresponding to the month. How to compute a date which is 6 months from today? More about the indispensable Status(CurrentDate) function below.
Date (Month (Status(CurrentDate) + 6, Day (Status(CurrentDate), Year (Status(CurrentDate))
As noted above, FileMaker will increment the Year if month+6 is > 12.
MonthName (date)
How to find all people in your database with a birthday in July? Create a calculated text field
MonthName (BDate)
or a calculated numeric field
Month (BDate)
and then Find all records with the first equal to "July" or the second equal to 7. For user-friendliness, use MonthName and let the user select the month he wants from a popUp menu of all months, in chronological order. Either perform a Find or make a self-relation based on the MonthName field, and show the results in a portal. But maybe that's an "Intermediate" level topic...
Today
This is in FileMaker Pro 3 for backwards compatibility with earlier versions of the program. Because of the fact that it reevaluates when a file is opened, and the slow and inefficient way it recalculates, its use has largely been supplanted by the Status(CurrentDate) function.
Year (date)
The numeric field "IsLeapYear" in the following calculation:
Set Field (IsLeapYear, DayofYear (12, 31, Year(ThisYear)) - 365)
evaluates as "1" (true) when the field ThisYear contains a leap year date, and "0" (false) otherwise. You want to figure out the date for next Saturday, no matter what today is? In this calc, "next Saturday" = today, if today is Saturday:
Date ( Month (Status(CurrentDate), Day (Status(CurrentDate) + 7 - DayofWeek (Status(CurrentDate)), Year (Status(CurrentDate))
We mustn't forget the all-important age calc:
Set Field (Age, Year (Status (CurrentDate)) - Year (BirthDate) - (Date (Month (BirthDate), Day (BirthDate), Year (Status (CurrentDate))) < Status (CurrentDate))
Here's another version, posted by Don Wieland on the FMPro List, that puts the age into a text field, in the form "23 years, 10 months, 16 days":
Year(Status(CurrentDate)) - Year(Bday) -
(Status(CurrentDate) < Date(Month(Bday), Day(Bday),
Year(Status(CurrentDate)))) & " Years, " &
(Mod(Month(Status(CurrentDate)) - Month(Bday) + 12 -
(Day(Status(CurrentDate)) < Day(Bday)), 12)) & " Months, " &
(Today- Date(Month(Status(CurrentDate)) - (Day(Bday) >
Day(Status(CurrentDate))), Day(Bday), Year(Today))) & " Days"
And someone asked recently for a "fiscal year" formula. Let's consider the F.Y. ending June 30. Define "Fiscal" as an unstored text calculation:
If(Month(Date) > 6,
Year(Date) & "/" & Right(Year(Date) + 1, 2),
Year(Date) - 1 & "/" & Right(Year(Date), 2))
This gives "1998/99" if Date contains 30 June 99, and "1999/00" if Date is 1 July 99. That one comes from Brian Dunning on the FMPro List.
WeekofYear (date)
Someone's got a use for this one. Returns an integer as high as 54, depending on the year.
WeekofYearFiscal (date, start)
Okay, so this must be for the accountants. You provide a date and a number from 1 thru 7 which sets the starting day for your fiscal year. The result again may be 1 thru 54.
One more useful formula, to compute the number of working days (Monday thru Friday) between two dates. This is from Chris Moyer's book, Special Edition Using FileMaker Pro 3 for the Mac:
Int((End Date - Start Date) / 7) * 5 + Mod(End Date - Start Date, 7) - If(DayofWeek (End Date) < DayofWeek (Start Date), 2, 0)
The Time Functions
Internally, FileMaker Pro records times in terms of seconds (1:00:00 AM is 3600, 2AM is 7200, and so on) but displays them in a variety of user-specified formats. The default format is HH:MM:SS. If only two numbers are present with a single colon separating them, it is assumed that the two numbers represent hours & minutes. If only a single number is present, it's the hour.
FileMaker's internal representation of times makes it easy to compute time elapsed between two times; assuming that Time1 and Time2 are both on the same day, and that the fields to be set are numeric:
Set Field (Seconds_Passed, Abs (Time1 - Time2)) = seconds
Set Field (Minutes_Passed, Abs (Time1 - Time2) / 60) = minutes + fraction
Set Field (Hours_Passed, Abs (Time1 - Time2) / 3600) = hours + fraction
If the two times are known to be within 24 hours, with Time1 the start time, and you want the output to be in the form of HH:MM:SS rather than seconds, try this one:
Set Field (Time_Elapsed, If (Time2 > Time1, Time2 - Time1, Time2 - Time1 + 86400)
Hour (time)
Expects a valid time, but since FileMaker Pro records time in terms of numbers, any numeric value will be permitted - though the results may not be what you expect. Returns a number, based on the 24-hour clock. Of course there's no restriction on the range of the numbers in a time field:
Hour (14:30:45) is 14
Hour (34:30:45) is 34
Hour (-4:30:45) is -4
*The above notation is for illustrative purposes only. 14:30:45 as written will be construed by FileMaker as a field name! "14:30:45" will be taken by FM as a text string. The safe way to enter a "time constant" value is:
Hour (TextToTime ("14:30:45"))
and then FileMaker gets persnickety about valid time entries as well. More commonly you'll be using:
Hour (Completion_Time)
Hour (Status (CurrentTime))
or some other time-field name or time calculation, as your input.
Minute (time)
Expects a valid time, but since FileMaker Pro records time in terms of numbers, any numeric value will work.
Minute (14:30:45) is 30
Minute (34:30:45) is 30
Minute (-4:30:45) is -30
Seconds (time)
Expects a valid time, but since FileMaker Pro records time in terms of numbers, any numeric value will work.
Seconds (14:30:45) is 45
Seconds (34:30:45) is 45
Seconds (-4:30:45) is -45
Time (hours, minutes, seconds)
Similar to the Date function, and accepts negative and fractional values for any of the 3 inputs. Thus:
Time (23,19,45) is 23:19:45, or 11:19:45 PM
Time (23,-19,45) is 22:41:45, or 10:41:45 PM
Time (23,19,-45) is 23:18:15, or 11:18:15 PM
Time (23.5,19,45) is 23:49:45 or 11:49:45 PM
Get used to thinking of time readings as not just "What time is it?", but also "How much time has elapsed?" That way, you won't be surprised to see negative values, or hours greater than 23, in time fields. FileMaker does check validity for you. The following are correct time values when typed into a time field:
14:40
-14:40
38:40:59
4:40:25 PM
4:95:25
4:95:25 PM
4PM
The following time readings will generate a FileMaker error dialog when typed into a time field. The presence of "AM" & "PM" forces FileMaker to evaluate them as time of day rather than elapsed time, and the program doesn't like negative or fractional parameters within time data:
14:40 AM
-14:40 PM
4:-30:45
4.5 PM
even though it's happy to accept them as part of the Time function's data. Of course, non-numeric characters in a time entry also are rejected.
To compute the time 6 hours before a specified time:
Time (Hour (Spec_Time) - 6, Minute (Spec_Time), Seconds (Spec_Time))
which may return a negative number, but if you've formatted the field to display a specific format (rather than "show data as entered" which you would choose to show time elapsed) then it will convert the result to a valid time.
TimeToText (time)
Turns the time data into a text string. As with DateToText, this is most useful when searching for a range of times. If you have a time field, "Time" on your current layout, and you want the user to be able to press a button labelled "Times before 4 PM", attach this script to your button:
Enter Find Mode (No restore, no pause)
Paste Result[Select, "Time", "<" & TimeToText("4:00 PM")]
Perform Find
and you end up with all records whose field "Time" is less than 4 PM. Note that this includes all fields with negative times. If want to restrict it to after midnight and before 4PM:
Paste Result[Select, "Time", TimeToText("00:00:01" & "..." & TimeToText("4:00 PM")]
Combining Dates & Times
FileMaker doesn't make this simple for the programmer. There's no direct way to combine date & time in a single field and work with the combination. So, to compute elapsed time between Date1/Time1 and Date2/Time2, one may use an indirect method. Create two unstored calculations:
Days_Diff (Number) = Int (Abs (Date1 + Time1/86400 - Date2 - Time2/86400))
Time_Diff (Time) = Round ((Abs (Date1 + Time1/86400 - Date2 - Time2/86400) - Days_Diff) * 86400, 0)
Note that this converts a Date/Time combination to a number, Date+Time/86400, and computes the difference by subtracting one from the other. 86,400 is the number of seconds in a day. If you want to format your output nicely for your user, try this text calc:
Days_Diff & " days, " & Hours (Time_Diff) & " hours, " & Minute (Time_Diff) & " minutes, and " & Seconds (Time_Diff) & " seconds."
FileMaker Pro makes Date & Time math almost straightforward, once you understand what it is you're trying to do. Hopefully this article, by pointing out some of the pitfalls of messing with the Date-Time continuum, has helped you get a handle on the subject. :-)
## END ##
This article is part of a series aimed at those who are not yet experienced with FileMaker Pro. If you have a novice-level request that you'd like to see addressed, email the author at lornew@wimsey.com