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:

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:

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:

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:

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:

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.

Related

Категории