

Point in Time

Level: Beginner Version: FileMaker 17 Category: Calculations Tuesday, November 13, 2018 
Just about every database contains at least one date, time and timestamp field. Just think about the housekeeping fields for starters. Every table should contain a date and time when a record was created and modified to track record maintenance. I also add manually entered and scripted date fields to almost every table to track when events occur. It ends up being a lot of date fields! While most of your fields will be text, date fields are more commonly calculated and therefore more important to understand. When I teach a beginner course on FileMaker calculations, I always cover the function groups for text, date, time and logical because they are the most important. Being able to manipulate date and time data, but especially dates, will help you become a successful FileMaker developer so study this article carefully.
Date and Time Storage The most important thing to remember about the date and time functions are how FileMaker stores them. Dates are stored as a number of days since the beginning of FileMaker time or January 1st, 0001. That means determining a date in the future is as easy as adding a number of days. To understand how dates are stored, a simple conversion of a date to a number by specifying a number result in a calculation is all you need to do to reveal the numerical value behind the date.
3/22/18 = 736,775
Time is stored as the number of seconds that have elapsed in a given day, starting at 12:00:01 AM. There are 86,400 seconds in a single day. Divided by 24 gives you 3,600 seconds in an hour. Divided again by 60 returns 60 or the number of seconds in a minute. With this knowledge, you can determine any past or future time by adding or subtracting the number of seconds as a number value. Converting a time to a number shows the numerical value.
1:00:00 PM = 46,800
Timestamps are stored as the number of seconds since the beginning of FileMaker time. Another way to look at it is to take the number of days since the beginning of FileMaker time multiplied by 86,400 plus the number of elapsed second for the current day. Here's a timestamp converted.
11/6/2018 1:00:00 PM = 63,677,106,000
Basic Date Formulations Calculating with basic date conditions couldn't be easier once you know how they are stored. For example, let's say you want to add a due date for every invoice that is thirty days in the future. The formula for "date_due" couldn’t be easier:
date_posted + 30
Don’t forget to set the calculation result to date, instead of number, or you get the number of days since the beginning of FileMaker time. If you want to know the number of days between the order date and the shipping date, simply subtract the two dates (it's kinds like subtracting two numbers):
date_shipped  date_ordered
Make sure the result type of the calculation is number! No matter how good your formula, it can be sidetracked by the incorrect result type. Sometimes the result will be erroneous or a question mark. Other times the error is more subtle, preventing proper searching and sorting. As a precaution, always set the result type before typing your formula into the calculation dialog!
In a more complex example, let's say you have an invoicing solution and want to determine the elapsed days even if there's no shipping date. In the example above, the formula will produce a negative number. Better to use the current date until the shipping date is entered:
Case(
IsEmpty(date_shipped);
Get(CurrentDate)  date_ordered;
date_shipped  date_ordered
)
The calculation above can be simplified by referencing the "date_ordered" field once. This is beneficial in many ways including a more compact formula and easier updating because there is only a single "date_ordered" reference.
Case(
IsEmpty(date_shipped);
Get(CurrentDate);
date_shipped
)  date_ordered
FYI: In the example file that comes with this article, the date fields are generically referred to as "date1" and "date2", "time1" and "time2" and "stamp1" and "stamp2".
Date Construction Constructing date results using concatenation will lead to errors when dates cross years or a leap year is encountered. In addition, concatenated dates don't work across countries when the day and month are reversed (mm/dd/yyyy vs. dd/mm/yyyy). For these reasons, it's smart to construct dates using the Date function, even if it requires more work. For example, here's a concatenated date 30 days in the future:
Month(Get(CurrentDate))
& "/" &
Day(Get(CurrentDate)) + 30
& "/" &
Year(Get(CurrentDate))
Not only does this formula fail across years and during leap years, it also gives the wrong answer across months. If the date today is "9/7/2018" then the answer from this equation is "9/37/2018". Just a little more work with the Date function provides an accurate answer:
Date(
Month(Get(CurrentDate));
Day(Get(CurrentDate)) + 30;
Year(Get(CurrentDate))
)
If the date happens to be "12/20/2018" then the correct answer of "1/19/2019" is returned. If you ship your solution oversees to England then the answer will be "19/1/2019". The moral of this story is always use the Date function to construct dates.
So... why not just add 30 days to the date like was shown previously? Good question! The first reason is I wanted to simplify the example for a better learning experience. Let's make the formula a little more complex to demonstrate why you'll need this technique. What if you want to determine the last day of the current month. It's not possible by simply adding days since you don't know how many days are in the current month and FileMaker has no function called "DayLast". In order to determine the last day of the current month, the Date function is required:
Date(
Month(Get(CurrentDate)) + 1;
1;
Year(Get(CurrentDate))
)  1
By adding 1 to the month and specifying "1" as the day, the first day of the next month can be determined. Now all you have to do is subtract one day from the resulting date to get the last day of the current month. Even in a leap year, this formula will always give you the last day of the supplied date.
In the example above, if the calculation is not set to unstored, the current date will never update, causing the formula to always result in the last day of the month when it was first created. Formulas that don't reference data entry fields should be specified as "unstored". Otherwise, they won't update from the initial value that was calculated upon exiting Manage Database. If a calculation field references a data entry field then it will update every time the data entry field is changed. Otherwise, the result is simply stored and not recalculated in order to improve performance. In an unstored calculation, the result is calculated every time the screen refreshes which occurs when navigating records, changing layouts, opening a file or any time the field is displayed or redisplayed.
That doesn't mean every calculation field should be set to unstored! If FileMaker has to recalculate a formula every time the screen refreshes then it will slow down your solution as you add more unstored calculations. Other features are automatically unstored such as Placeholder Text, Hide Object and Conditional Formatting. Add all these unstored calculations together and you have a recipe for disaster. So... just use unstored calculations when needed for the best performance!
Scripted Date Finds A common scenario for manipulating dates is when performing a scripted find. Let's say you have an invoicing solution and want to locate overdue invoices. For simplicity, let's consider an overdue invoice as one that is thirty days old so we can focus on the technique and not get lost in the details. Many amateur developers attempt a solution by creating a calculation field in Manage Database and then search it with the criteria of "Unstored":
Case(
Get(CurrentDate)  30 > date_posted and status = "Unpaid";
"Overdue"
)
In order for this formula to update properly, it has to be set as unstored. Even though the formula references a stored field, that "date_posted" field is unlikely to update very often, if at all. If the formula is not set to unstored, it will never update and the invoice will never become overdue. Once it's set to unstored, it can longer be indexed, causing the find to be slow. Sure, the find will seem fine with a few records but will get slower and slower as more records are added. While it's fine to use this calculation field for display purposes, it's better to create a script to perform the find.
The important part to notice about this script is the overdue calculation field, shown above, is not being searched. Instead, the "date_posted" field, which is stored, is being searched, making the find as speedy as any other find. The formula is quite simple.
"<" & Get(CurrentDate)  30
I went a little crazy with the Show Custom Dialog step to support proper grammar. Since you can't see the entire formula, here it is (it's also in the example file that comes with this article):
Let(
@Count = Get(FoundCount);
Case(@Count = 1; "There is "; "There are ")
& @Count
& " overdue "
& Case(@Count = 1; "invoice"; "invoices")
& "."
)
The Let function is another way of creating a more efficient formula. By declaring Get(FoundCount) at the beginning and referring to it as @Count wherever it appears in the formula, the calculation is easier to read, more efficient and simpler to update when needed. Imagine if you needed to change Get(FoundCount) to Get(TotalRecordCount). Without the Let function, this would require three changes instead of one.
FYI: The at sign (@) is not required when declaring a variable but it helps to easily locate variables in the midst of a long formula as well as allow for reserved words like function names that would otherwise cause havoc.
Time Functions While dates work in days, time works in seconds. A simple example creates a calculation that adds fifteen minutes to a time field by adding 900 seconds.
time1 + 900
Don't forget to format your calculation fields with time results as 12 hour time or they will appear in 24 hour format. That's just the way calculations roll. On the other hand, time entered into a data entry field will appear as entered unless formatted otherwise.
More intriguing is the ability to round time for use in time card solutions. Once you understand how many seconds are in fifteen minutes, it’s easy to round to the nearest quarter hour:
Round(time1 / 900; 0) * 900
If you divide by 900, you get the number of fifteen minute increments. Let's say the time is 1:08 PM. Dividing by 900 results in 52.5333333333. Applying the Round function with 0 decimal points rounds to the nearest whole number of 53. If the time was 1:07 PM the rounded value would be 52. With a whole number of fifteen minute increments, multiplying by 900 again, results in the nearest quarter hour. Make sure to set the calculation result type to "time"!
In order to always round up to the greatest quarter hour, use the following formula:
Int((time1  1) / 900) * 900 + 900
The Int function truncates all decimal values, returning an integer. Before the Int function is applied, one second is subtracted from the time which prevents the time from rounding up to the next fifteen minute increment. This will happen if an exact quarter hour time is entered like 1:15 PM. Otherwise, the subtraction of one second is not needed. Essentially, the formula rounds the time to the lower fifteen minute increment and then adds fifteen minutes. So, you could modify the formula to always round down by removing the addition 0f 900 seconds.
Int((time1  1) / 900) * 900
It's pretty easy to determine elapsed time by subtracting two time fields and then formatting the calculation field to display in 24 hour format.
time2  time1
If the calculation field is not formatted in 24 time format, the result will be the same but it will read as time rather than hours, minutes and seconds.
If you want elapsed time over multiple days, the formula gets a little more complicated but not frustratingly so.
(date2  daye1) * 86400 + time2  time1
Again, format the calculation in 24 hour format via the Inspector. If timestamp fields are available, the formula becomes much easier.
stamp2  stamp1
As with the previous formulas, choose a time result for the calculation (not timestamp) and format the field on the layout as 24 hour time.
Advanced Date Formulas What if the "elapsed_days" calculation at the beginning of the article should only look at work days, excluding Saturdays and Sundays? No simple subtraction formula will solve the problem. A Case statement is the first possibility you should consider but could end up being a convoluted nightmare of multiple Case statements strung together. The best solution is the following formula, which limits the logical statements to a minimum.
Let(
[@Start = date1; @End = date2];
Int((@End  @Start) / 7) * 5 +
Mod(@End  @Start; 7) 
Case(
DayOfWeek(@End) < DayOfWeek(@Start);
2
)
)
To avoid creating an overly complicated formula, both dates need to be week days. It's a work day formula anyhow so it would be pointless to enter a weekend date. It's very important to think through the needs of a solution. Don't just program for all solutions. Program for your solution so it's easier to create.
The first line of the formula uses the Int function to remove two days out of every complete seven day span, giving you a number of work days for the full weeks. The second line returns the number of days in the incomplete seven days span using the Mod function. The Mod function returns the remainder of a division or the days in the incomplete seven day period.
The Case statement aims to add a weekend that was missed by the rough calculations from the first two lines. If the day of the week for the shipping date is less than the day of the week for the posted date, an extra weekend must have occurred during the partial week. Luckily, with this approach, there's only one Case statement. Phew!
Even more complex is the formula that determines a finish date given a start date and an number of work days. Again, the start date has to be a work day and the approach is to limit the number of logical statements.
Let(
[@Start = date1; @Days = work_days];
@Start + Int(@Days / 5) * 7 +
Middle(
"12345012340123601256014560345623456";
(DayOfWeek(@Start)  1) * 5 + Mod(@Days; 5) + 1;
1
)
)
The first line computes seven days for every five day work week, giving a rough estimate of the number of days. This is done by dividing by five and then multiplying by seven... similar to what was being done in the previous formula but in reverse.
The Middle function is where the magic happens. The idea behind this crazy code in the first parameter is to determine how many days need to be added to each day of the week in order to create each day of the work week. For instance, take the first five numbers, "12345". These represent Sunday. Sunday needs one day added to make it Monday, two days for Tuesday, three days for Wednesday and so on. The next five numbers represent Monday ("01234"). Monday needs no days added to make it Monday, one day for Tuesday, two days for Wednesday and so on.
Once you understand the string of numbers, the second parameter of the Middle function is easy. It just determines which number to grab. I have to admit, I have a hard time understanding how the formula works. A friend named Steve Kurasch wrote the formula on a cocktail napkin without even blinking an eye. That’s just the kind of mind he has and we all get to benefit from his genius but we don’t always get to fully understand the results of his work. Just understand that the number that is picked adds a number of days in case the previous two lines return an ending date that is a weekend.
All Sorts of Formulas There's all sorts of formulas out there for dates, times and timestamps. If I don't know how to create a formula, I turn to Google. For instance, If I were trying to find a formula to determine age I would search for "FileMaker Age Calculation". The resulting hit list will contain dozens of valid age formulas for every conceivable variation you can think. The point is, you have the tools now to understand those date, time and timestamp formulas you find out there on the internet or even build your won. Happy FileMaking!
Author: John Mark Osborne jmo@filemakerpros.com www.databasepros.com
Example File (available to patrons at $5.00 per month): Download here if you are a patron
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:


Peter Fullerton

11/16/2018


Thank you for another chapter of experienceinformed, FileMaker "magic", John Mark. It strikes me that every now and again, there is a deceptively "simple", thought provoking, something extra. For my money, your "...Don't just program for all solutions. Program for your solution so it's easier to create...." is one of those extras. It evokes such a helpful state of mind for an interested amateur  on a steep learning curve  like myself. Thanks again.
Response by: John Mark Osborne

11/16/2018

So glad you are validating my message of simplicity. So many developers these days think more is better when less is usually better. As the famous architect Frank Lloyd Wright said, "Less is more only when more is too much."



Mauricio Navarro

11/14/2018


Hello: you wrote: "If the date happens to be "12/20/2018" then the correct answer of "1/29/2019" ". Answer would be right if the formula for day is Day(Get(currentdate))+40, not +30 as the sample shows. But I get the drift, though.
Response by: John Mark Osborne

11/16/2018

Once again, I thank you for reporting this error Mauricio. I truly appreciate the time and effort it takes to check my work and put together a comment. Have a great Thanksgiving. BTW, I changed the article to reflect your comment.



Anna Kochergina

11/14/2018


Date fields (and btw time fields) are the poor relative in databases / software solutions’ conception. It’s so true John! I really appreciated your very pedagogical article and could notice some of required calculations may be tricky! But the “Magic” 35 characters figure you provide to dramatically shorten your script in the Advanced Formulas’ chapter is indeed really something will never be able to do instead of human genius. I tried to find an alternate solution and the first idea I had was to replace this “Magic” by a 7x5 matrix “self calculated or populated” using both the Mod and GetCurrentDay() functions within a simple loop. What do you think dealing with such a method John? Anyway, many thanks for this tutorial which can be applied to FileMaker but many other RAD tools too relying on databases. Have a great day!
Response by: John Mark Osborne

11/14/2018

Thanks for your kind words. Glad you enjoyed the article. You could get a script to accomplish the same task. I think the difficulty occurs when you consider how you are going to run the script. If it's manually clicking a button then I have no issues. If by script trigger then I'm concerned with two possibilities. First, it could conflict with another script or script trigger. I try to limit the number I run so this doesn't happen. I discuss a specific example in my "Ultimate Find" article. The second concern is will it always run. It's not that you couldn't force it to run from PHP or attach it to every layout where data entry might occur, it's the difficulty of remembering to plug all the holes (e.g. when creating a new layout). In the case of a calculation field, it's schema level so it always runs no matter where the entry of data is done. Hope this answers your question.




