FM Function of the Week: Day Functions

Scanning the growing list of functions of the week, I noticed a lack of date and time functions. Dealing with temporal matters isn’t always easy. If time were a base-ten system with, say, ten hours in a day and a hundred minutes in an hour, calculating would be a lot easier. That said, most of the neuron straining elements of dealing with time are already baked into FileMaker. All you have to do it learn how to wield them. We’re going to start with three day functions.

Day ( date )

The Day function hands back the numerical date of the date in its parameter. Put like that, it probably sounds a bit useless. If you’re putting a date in the parameter, you already know the number of the day in question.

Day ( “10/31/2016” ) = 31 (= Duh!)

The above use of Day will work, but it’s true purpose is figuring out the day from a date value in a field. Suppose you have a process that must run on the first day of every month. Combining Day with Get ( CurrentDate ) will derive the current day of the month (any month). Now you just have to see if the result of the calculation below equals 1 and you can go from there.

Day ( Get ( CurrentDate ) )

One more scenario. You’re the party planner for a big company that throws a little birthday observance at the middle and end of each month. To get the right names on the cake you need to know whose birthdays fall on the first through the 15th, and whose come in the second half.

If (   Day ( People::Birthdate ) < 16; “Mid-month Cake”; “End of Month Cake” )

If you’ve already got individuals’ birthdays in your database, the calculation above neatly classifies each person according to which of the two monthly they’ll be feted.

DayOfWeek ( date)

This function will give you a numerical value for the day of the week that the provided date falls upon. It’s an efficient way to, say, determine whether a date falls on a weekend. Or if you have tasks for certain days of the week, DayOfWeek can be used to enforce that they only happen the proscribed day. DayOfWeek assigns 1 to Sundays, 2 to Mondays, and so on. (If I ran the zoo we’d assign 1 to Mondays so that weekend numbers would be consecutive, but there’s probably a good reason why I’m not in charge.)

DayOfWeek (

April 12th, 2003 happened to fall on a Saturday, so DayOfWeek assigns it a value of seven. You take the concept a bit further to check and adjust dates based on the day of the week. Suppose you have automatically-created shipping dates in an order system. DayOfWeek can be employed to check the default shipping dates and, if they land on a weekend, bump them back to the following Monday.

A Case function that checks for weekend dates and adds one or two days, changing them to Monday dates.

This one takes a bit more unpacking. If you’re not familiar with the Case function, check out this post first. The Case “wrapper” essentially lets us test different scenarios and provide an outcome for each. Line 2 of the calculation asks “does the date in the shipDate field fall on a Sunday?” If the answer is true, line 3 is invoked. Line 3 adds one day to the shipDate, giving us a Monday date and FileMaker ignores the rest of the calculation.

If the answer to the question posed in Line 2 is false, FileMaker ignores line 3 and skips to line 4 (I’m ignoring blank lines for numbering purposes). Line 4 asks “does the date in the shipDate field fall on a Saturday?” If that answer is true line 5 is evaluated giving us a Monday date, this time by adding two days. As before,if Line 3 was true, FileMaker stops calculating at this point.

Should the questions posed on Lines 2 and 4 both be false FileMaker evaluates Line 6, known as the default result. Line 6 essentially says “keep the original ship date”. If we get to Line 6, we’ve already determined that the ship date isn’t on a Saturday or a Sunday, so it must fall on a weekday where no change is necessary.

 DayName ( date )

Knowing what you now know about DayOfWeek and Case (or Choose, if you’re really fancy), you’ve probably already figured out how to turn DayOfWeek results into the names of the days of the week. But FileMaker has baked that logic right into its own function. Want to know what day you were born on? Put your date of birth into the parameter of a DayName function and FileMaker will reveal it to you. Using DayName, you can calculate a message like “Your order will be shipped on Thursday, April 24”.

This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.