FileMaker can be a little esoteric about dates and times. If you don't understand how they work, you can end up wasting a
of time trying to do things that FileMaker can easily do for you. For example, you might need to know the first day of the month following the date an invoice is due. You can
Date ( Month ( Invoice Due Date ) + 1 ; 1 ; Year ( Invoice Due Date ) )
Before you starting 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/2005" or "10:23 AM." As with other
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 really big number).
This secret to date and time storage isn't just a technicality. It actually
Because FileMaker looks at dates and times as numbers, you're free to use them right along with other numbers and operators in all kinds of mathematical functions. By adding, subtracting, multiplying, and dividing dates, times, timestamps, and numbers, you can come up with meaningful results.
You can use the information in your database's date fields to have FileMaker figure out due dates, anniversaries, and so on. You can use date fields and numbers interchangeably. FileMaker's smart enough to figure out that you want to add whole days to the date value it's storing. Here are some general principles:
To get a date in the future or past, add or subtract the number of days. For example, if your policy is that payments are due 10 days after invoices are presented, use this calculation:
Invoice Date + 10
Of course you aren't limited to adding constant numbers to dates. You can add a number field to a date field just as easily. If your video rental database holds the checkout date and the rental duration, you can find the due date with this calculation:
Checkout Date + Rental Duration
To get the number of days between two dates, subtract them.
Imagine your registration database holds arrival and
Departure DateArrival Date
Although FileMaker's internal clock counts time as the number of seconds since midnight, a time value doesn't always have to be a time of day. Depending on the field format (Section 188.8.131.52), a time value can be a time of day, like 2:30 PM, or a time (as in duration, like 3 hours, 27 minutes).
In both cases, times have a numeric value, in hours:minutes:seconds format. 14:30:05 in a time of day field is 5 seconds after 2:30 PM, but in a time field, it's a duration of 14 hours, 30 minutes, and 5 seconds. If the time has
You can record how long your 5-year-old takes to find her shoes (34:26:18), or how long she takes to find the Halloween
The key to doing math with any kind of time value is to remember you're always adding and subtracting amounts of seconds . Here are the guidelines:
To get a time in the future or past, add or subtract a number of seconds or a time value. If you know when a student finished her exam, and you know how long the exam took in minutes (1 minute = 60 seconds), you can figure out when she started.
Finish Time(Exam Duration * 60)
To get the number of seconds between two times, subtract one from the other. Your Test Reporting database stores start and finish times for each exam. To find the duration, use this calculation:
Finish TimeStart Time
To get a time of day value in the future or past, add or subtract the number of seconds or a time value. Suppose you have a database of movie show times for your theater business. You use a timestamp field to record the date and time when each showing starts. You also use a time field to keep track of how long each movie is. Now you need to know when each movie ends :
Showtime + Duration
To get the number of seconds between two timestamp values, subtract one from the other. For example, you use timestamps to record the date and time you start and finish a job. To find out how long the job took, in minutes, use this calculation:
(Finish Time StampStart Time Stamp) / 60
To increase or decrease a time duration value, add or subtract the number of seconds, or another time duration. Say you use a repeating time field to hold the length of each song on a CD. This calculation tells you how long the entire CD is:
Sum ( Songs::Song Lengths )
To double, triple, halve, or
If chilling your microbrew always takes twice as long as cooking, you can determine the chilling time with this calculation:
Cooking Time * 2
Just as you can parse out bits of text from text fields, FileMaker lets you pull out
With a date value, you can use Year, Month, and Day. If you have a time, Hours, Minutes, and Seconds apply. You can use all six functions with a timestamp value.
These functions all have the same form. Each takes a single parameterthe valueand returns a numerical result. For example, the Day function returns the day portion of a date. This calculation returns 27:
Day ( GetAsDate ( "7/27/2006" ) )
UP TO SPEED
From Numbers to Times
If you can treat dates and times like numbers, it only makes sense that you can go the other way too. Suppose you have a field called Race Time that holds each athlete's race time as a number of seconds. If you'd rather view this time in the Hours:Minutes:Seconds (or Minutes:Seconds) format, you can easily use a calculation to convert it to a time value:
The GetAsTime function, when you pass it a number value, converts it into the equivalent time.
(If you view this on a layout, you can use the time formatting options to display hours, minutes, and seconds in just about any way you want, as shown on Section 6.6.3.) The GetAsTime function has another purpose as well: It can convert text values into times. If someone puts "3:37:03" into a text field, you can use GetAsTime to convert that text into a valid time value.
FileMaker has GetAsDate and GetAsTimestamp functions too, which work just the same.
UP TO SPEED
Even when you're using the month number to
MonthName ( Invoice Date )
This calculation returns "March."
You can still
Sometimes you need to see the day name (Monday, Tuesday, or Wednesday, for example). The DayName function does just that. To get its numerical equivalent, use DayOfWeek instead, which returns 1 for Sunday, 2 for Monday, and so forth.
Almost every database in existence has fields that create date and time values. Otherwise, folks would still sit around pecking out business forms with a typewriter, an adding machine, and a wall calendar. It sounds quaint, but there was one advantagethe human brain. Without even being aware of it, people do incredibly complex math every time they glance at a paper calendar or analog clock. When the boss said, "I want these invoices to go out two days after the end of next month," a human clerk knew exactly what to do.
When you work with dates and times in FileMaker, sometimes you can get away with just simple math as in the previous section, plugging date and time values into basic calculations. But how do you tell a computer to put "two days after the end of next month" in the Invoice Date field? FileMaker provides three functions to assist the translation:
The Date function accepts three parametersMonth, Day, and Yearand returns the appropriate date value. For example, to put a date value of January 21, 2006 in a calculation, you use the date function like this:
Date ( 1 ; 21 ; 2006 )
The Time function wants three parameters as well, this time Hours, Minutes, and Seconds. It returns the time value. (The Seconds parameter can have a decimal point if necessary.) For example, you can construct the time value "8:00 PM" like this:
Time ( 20 ; 0 ; 0 )
The Timestamp function takes just two parameters: Date and Time. It combines the two into a single timestamp value. It shows January 10, 2006 at 8:30 p.m. like this:
Timestamp ( Date ( 1 ; 10 ; 2006) ; Time ( 20 ; 30 ; 0 ) )
In this example, you use all three functions: the date function to produce the correct date value, the time function for the time value, and the timestamp function to put them together.
FREQUENTLY ASKED QUESTION
Why can't I just put "1/10/2006" in my calculation, just like I'd put it in a date field ?
Because "1/10/2006" is a text value, not a date value. When you're entering data in a date field, FileMaker
You can use the GetAsDate() function to convert text values like this into dates:
GetAsDate ( "1/10/2006" )
But even this isn't advisable. Remember from Section 184.108.40.206 that dates are interpreted depending on how you've configured your computer? On one computer, this calculation could produce the date value January 10, 2006, while on another machine it might result in October 1 instead. In other words, there's no safe way to ensure you get the date you really want when you use GetAsDate with a text value, unless you're using text the
The Date function always expects the month, then the day, then the year. Computer settings don't affect it. So the date function is the safest way to record dates in calculations.
Although FileMaker doesn't look at calendars the way people do, that's not all bad. You see a calendar in absolute terms: April 30 belongs to April, May 1 belongs to May, and that's that. FileMaker, however, thinks of dates in relative terms and sees no such limitations. You can use this flexibility to your advantage in calculationsbig time. You can give seemingly illogical parameters to the date function, and have FileMaker produce a valid date anyway.
For example, this calculation actually produces a valid date:
Date ( 5 ; 0 ; 2006 )
You see a
These same smarts apply to the month as well:
Date ( 15 ; 11 ; 2006 )
Produces March 11, 2007 . In other words, three months into the next year, since 15 is three months more than one year.
This behavior comes in super-handy when you're trying to fiddle with dates in calculations. Suppose you have order records, each one with an order date. You bill on the last day of the month in which the order was placed, so your calculation needs to figure out that date, which could be 28, 30, or 31 depending on the month, or even 29 if it's February in a leap year. That calculation would take an entire page in this book. But here's a much easier approach: Instead of calculating which day each month ends, use the fact that the last day of this month is always the day before the first day of next month. To start with, you can calculate next month like this:
Month ( Order Date ) + 1
So the date of the first day of next month is:
Date ( Month(Order Date) + 1 ; 1 ; Year(Order Date) )
To get the day before, just subtract one from the whole thing:
Date ( Month(Order Date) + 1; // the _next_ month 1; //the _first_ day Year(Order Date) // the same year )- 1 // subtract 1 to get the day before
It may look a little confusing at first…but it's much shorter than a page. And it works perfectly every month of every year.