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 might 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 ) )
10.4.1. How FileMaker Looks at Time
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 numbers that it stores one way and displays another, FileMaker does the math on the stored value, then converts it for your convenience. Here's how FileMaker keeps track of time:
This secret to date and time storage isn't just a technicality. It actually tells you a lot about how you can use dates and times in calculations. In the next few sections, you'll see how you can use simple math to do temporal magic.
10.4.2. Math with Dates and Times
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:
Invoice Date + 10
Checkout Date + Rental Duration
Imagine your registration database holds arrival and departure dates. You can find the duration of the stay (in days) using this calculation:
Departure DateArrival Date
Note: When you're adding a number to a date, the result is a brand new date, and you should set the result type (Section 184.108.40.206) of your calculation accordingly. On the other hand, if you're subtracting two dates, the result is a numberthe number of days between the two dates. In this case, set your calculation to return a number result.
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 220.127.116.11), a time value can be a time of day, like 2:30 PM, or a time (as in duration, like 3 hours, 27 minutes).
Tip: FileMaker is savvy to the concept that time passes, but not all programs are. For instance, if you're exporting data to Excel, you should first convert time fields containing durations to plain old number fields.
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 fractional seconds (a decimal point), the numerical value does too.
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 candy (00:00:02.13).
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:
Finish Time(Exam Duration * 60)
Finish TimeStart Time
Showtime + Duration
(Finish Time StampStart Time Stamp) / 60
Sum ( Songs::Song Lengths )
If chilling your microbrew always takes twice as long as cooking, you can determine the chilling time with this calculation:
Cooking Time * 2
10.4.3. Parsing Dates and Times
Just as you can parse out bits of text from text fields, FileMaker lets you pull out parts of a date or time value. For example, you might keep track of all your employee's birthdays in a normal date field, but you're trying to get statistical data from the year they were born, so you're not concerned about the month or date part of that value. You have six functions at your disposal to pick those individual components from a date, time, or timestamp value. They are:
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.
Tip: For advice on how to display the results of month and day values in plain English, see the box below.
|UP TO SPEED
Name the Day (or Month)
Even when you're using the month number to group your data, you may prefer to see months by name. For example, if you produce a report of sales by month, you probably want the groupings labeled January, February, March, and so on, instead of 1, 2, and 3. You can use the MonthName function to get this effect:
MonthName ( Invoice Date )
This calculation returns "March."
You can still sort all your invoices by the date field to get them in order, but you use your new MonthName value to display in the sub-summary part. See Section 18.104.22.168 for details on using sub-summary parts in reports.
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.
10.4.4. Calculations that Create Dates and Times
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:
Date ( 1 ; 21 ; 2006 )
Time ( 20 ; 0 ; 0 )
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 knows it's a date field, and is nice enough to convert text like this into a date for you. In a calculation, though, FileMaker may not know you want a date, so it treats what you put in as text instead.
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 22.214.171.124 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 user supplied.
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.
10.4.4.1. The secret powers of date
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 nonsense resultMay 0, 2006. But FileMaker looks at the same code and says, "No problem. Zero comes before 1, so you must mean the day that comes before May 1." And so it returns April 30, 2006.
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.
Part I: Introduction to FileMaker Pro
Your First Database
Organizing and Editing Records
Building a New Database
Part II: Layout Basics
Advanced Layouts and Reports
Part III: Multiple Tables and Relationships
Multiple Tables and Relationships
Advanced Relationship Techniques
Part IV: Calculations
Introduction to Calculations
Calculations and Data Types
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help