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 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:

• 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 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.

10.4.2.1. Dates

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 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 9.3.1.5) 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.

---

10.4.2.2. Times

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 3.2.6.4), 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:

• 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
  

   

  ---

  Note: If you store the date and time the movie starts in separate date and time fields, the movie time calculation is much more difficult. Suppose a movie starts at 11:30 p.m. and runs for two hours. Adding these together, you get 25:30, which is a perfectly valid time value, but not a valid time of day. When you add to time values, they don't "roll over" after midnight. Timestamps, on the other hand, work as expected: You get 1:30 a.m. on the next day.

  ---

• 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 otherwise scale a time duration, multiply or divide it by a number.

  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:

• Year
• Month
• Day
• Hours
• Minutes
• Seconds

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:

GetAsTime(Race Time)

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 6.9.4.7 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:

• 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 )
  

   

  ---

  Note: For time-of-day values, the Time function doesn't let you specify a.m. or p.m., so you have to use 24-hour notation.

  ---

• 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 Functions?

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 3.2.6.4 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.