Mastering Date and Time Calculation
Forms rely on the Date Picker field a lot - Whether you're:
🔘 Assessing turnaround times of projects
🔘 Dealing with registrations
🔘 Handling reservations
There will always come a time that you'd have to compute the difference between two dates. When the situation calls for it, you may need to compute the difference up to the last minute.
This article will guide you along the process of doing just that. Keep in mind this guide is never intended to help you build the entire form, it's only meant to give you the idea on how to perform calculations with 2 date fields.
First, we need to talk about Epoch Time. What is it?
The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'.
Definition cited from https://www.epochconverter.com/
It's important that you're comfortable working with the Form Calculation Widget. If you're not, we strongly recommend you get yourself acquainted with this feature by reading this guide. Moving on, here's a table showing the complete breakdown of each unit of human readable time into seconds that we will use with our calculations:
Human readable time | Seconds |
1 minute | 60 seconds |
1 hour | 3600 seconds |
1 day | 86400 seconds |
1 month (30.44 days) | 2629743 seconds |
1 year (365.24 days) | 31556926 seconds |
Take note of the conversion units above since these will be used in our calculations later on. Also, here are some key points on how the Form Calculation Widgets will be used, why certain formulas are needed, and so on:
🔘 The difference needs to be in Epoch Time format. Why? Because when subtracting two dates, the default difference is in the unit of days. To convert a day into Epoch Time (seconds), we'll multiply it by 86400.
Formula:
(Date2-Date1)*86400
🔘 Remainders are important! Your calculations will mostly revolve around divisions and remainders are essential in getting the excess (remaining) number of months, days, hours, and minutes.
To put it in perspective, take 12345 as an epoch time. That is equivalent to 3 hours, 25 minutes and 45 seconds.
To get the result of 3 hours:
12345/3600=3.4291666667OR 3 R. 1545
And to get the result of 25 minutes:
1545/60=25.75OR 25 R. 45
🔘 The widget doesn't offer a way to get the exact remainder from a division operation. Some of you may be familiar with the % or MOD operation in most programming languages but this is not available in the widget. As an alternative, we'll use the decimal place of the quotient to get the remainder.
Example:
40/6=6 R. 4
40/6=6.6666666667
Both answers above are correct and from there we get the following:
Whole number: 6
Decimal place: 0.6666666667
Remainder: 4
To convert a decimal place to a remainder, we just multiply it back with the divisor, which is 6. So:
0.6666666667x6=4.0000000002
Then we round it off to 4.
We'll use the floor() function to get the whole number of the quotient. It's worth mentioning that floor() is round down, ceil() is round up while round() is round off. This will then be subtracted from the quotient to get the decimal place (as shown on the example above).
Related guide: Form Calculation Math Function Reference
🔘 The epoch time difference should be divided by 2 divisors:
- 31556926 is the divisor for the Year, Month and Day denominations
- 86400 is the divisor for the Hour and Minute denominations
🔘 Hide the fields you don't want to show up on your form. Normally, you wouldn't need to show all the calculation widgets (e.g. the epoch time difference, the remainder placeholder, and so on).
Here's the demo form that will be used in this guide: https://www.jotform.com/71752744778975 so let's get started.
1. Add all the fields needed
🔘 Two Date Picker Fields
🔘 Form Calculation Widgets
Two or three calculation widgets are always required to be added - The Epoch Difference and the Remainder (for either the year/month/day or hour/minute denominations or both). The rest is upon your discretion and will highly depend on your requirement.
Example:
If you only need to get the difference in hours and minutes, you only need 4 calculation widgets:
- Epoch Difference
- Remainder for the hour/minute
- Hours
- Minutes
If you need to get the difference in months, days, hours, minutes, you would need 7 calculation widgets:
- Epoch Difference
- Remainder for the month/day
- Remainder for the hour/minute
- Months
- Days
- Hours
- Minutes
The demo form above uses 13 Form Calculation Widgets:
- 3 widgets required (Epoch Difference and 2 Remainders)
- 5 widgets (actual difference in years > months > days > hours > minutes)
- 5 widgets (breakdown of each unit of time for years, months, days, hours, minutes)
2. Setup the Date Picker Fields
Open the Date Picker Field's properties window and go to the TIME tab. Enable the TIME FIELD by toggling it to ON. Set the MINUTE STEPPING to 1. Then set the TIME FORMAT to 24 HOUR.
3. Set the Form Calculation Widgets to READ-ONLY
This is optional but preferred to be enabled to prevent users from mistakenly changing its value while filling out the form.
4. The fun part, add the formula
All units of time will be covered here so just pick the formula you need for your form.
Given:
Date 1 = Jan 01, 2017 at 01:05
Date 2 = Jun 30, 2036 at at 20:50
Epoch = 615239100
Epoch Time Difference formula:
(Date2-Date1)*86400
Multiply the difference by 86400 to get the Epoch Time in seconds.
Remainder (for Years > Months > Days) formula:
(Epoch Difference / 31556926-(
floor(Epoch Difference/31556926)))*31556926
This may look complicated but all we're doing here is get the remainder. The Epoch Time was divided by 31556926 because that's the number of seconds there are in a year. We then subtract the floored quotient from the original quotient to get the decimal value. Afterwards, multiply it back to 31556926 to get the remainder.
Remainder (for Hours > Minutes) formula:
(Epoch Difference / 86400-(
floor(Epoch Difference/86400)))*86400
Same idea but this time, we divide it by 86400 to get the remaining hours/minutes in a day.
Years formula:
floor(Epoch Difference/31556926)
This one's straight forward, just divide the Epoch Time by 31556926 then round down the quotient.
Months formula:
floor(Remainder for Y/M/D/2629743)
Now, we'll use the Remainder for Y/M/D and divide it by 2629743 to get the number of months remaining.
Days formula:
floor(((Remainder for Y/M/D/2629743)-Months)*30.44
This has to be multiplied by 30.44 to get the number of days remaining.
Hours formula:
floor(Remainder for HH:MM/3600)
Divide the Remainder for HH:MM by 3600, the number of seconds there are in an hour. Then round down the result.
Minute formula:
round(((Remainder for HH:MM/3600)-Hours)*60)
Instead of using floor(), we'll use round() this time. The higher units of time requires the use of floor() to give way to the remaining (remainder) smaller units of time. And since minutes is the smallest, it just needs to be rounded off.
The next 5 formulas are optional in case the need arises. Here are the complete and standalone breakdown of all units of time.
Years:
floor(Epoch Difference/31556926)
Months:
floor(Epoch Difference/2629743)
Days:
floor(Epoch Difference/86400)
Hours:
floor(Epoch Difference/3600)
Minutes:
floor(Epoch Difference/60)
Yes, it may look confusing at first but once you get the hang of it, especially the part of getting the remainder and which conversion unit needs to be used, it will all make sense. 😊
Clone the demo form to get a better understanding of how the calculations above work.
Related guide: How to clone an existing form from a URL
Comments and suggestions are welcome below. If you have a question, post it in our Support Forum so we can assist you.
I am not trying to calculate a date, per se, just trying to set the date picker to a specific date via conditional logic. I have been searching for the correct format to send to the date picker field and it doesn't appear to be anywhere on the help site. Should it be sent the date in epoch format? I've tried guessing a couple formats and ended up showing the years 1899 and 1407, haha! :P Thank you!
Awful is all I have to say. One reason I want to quit this system. Dates should be easy. You simplify in the Tables, why not forms?
is it possible to calculate the number of years by using a 'Date Picker' field vs a 'Date Picker Month Year' field ? Please advise
Hello There, I saw you were able to add 1 year to a date in a question here, but I'm wondering if there is a way to add 1 hour to a time and then the result be summarised into a time format?
For example, 12.30pm + 1 hour ... RESULT: 1.30pm
Is it possible? Many Thanks.
I am just wondering if you can do a number of weeks calculation between two dates for accommodation purposes, especially if you are charging on a weekly basis?
Why do this? What is the significance of 1/1/70? What benefit is there is finding epoch time? Thank you
Thanks for the information.
How do I add 1 year to the date selected then display it as a next review date
Thanks, a great resource and much appreciated.
Martin