Mastering Date and Time Calculation

April 15, 2022

Forms rely on the Date Picker element 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 of 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’.

Epoch Converter – What is epoch time?

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 timeSeconds
1 minute60 seconds
1 hour3600 seconds
1 day86400 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 using the Form Calculation widget, the default difference is in the unit of days. To convert a day into Epoch Time (seconds), we’ll multiply the absolute difference by 86400:

abs(Date1 - Date2) * 86400

Remainders Are Important

Your calculations will mostly revolve around divisions and remainders are essential in getting the excess or remaining number of months, days, hours, and minutes. Using your keyboard, you can type in the percent “%” symbol in the calculation field to use the modulo operator. The modulo operation returns the remainder of a division.

form-calculation-widget-mod-min.png

For example, the expression 7 % 3 would evaluate to 1, because 7 divided by 3 has a quotient of 2 and a remainder of 1.

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 minutes result, use the formula: floor((EpochTime % 3600) / 60)

Given: EpochTime = 12345
= floor((12345 % 3600) / 60)
= floor(1545 / 60)
= floor(25.75)
= 25

We used the floor() function to get the whole number of the quotient. For example:

To get the result for hours, use the formula: floor(EpochTime / 3600)

Given: EpochTime = 12345
= floor(12345 / 3600)
= floor(3.4291666667)
= 3

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 so let’s get started.

1. Add All the Fields Needed

  • 2 Date Picker elements
  • Form Calculation widgets

For the purpose of this guide, we’ll be using a dedicated Form Calculation widget for the epoch difference (in seconds) or Epoch Time. The rest is upon your discretion and will highly depend on your requirement.

For example, if you only need to get the difference in hours and minutes, you only need 3 Form Calculation widgets:

  • Epoch Time
  • Hours
  • Minutes

Another example is if you need to get the difference in years, months, days, hours, and minutes like the demo form above, you would need 6 Form Calculation widgets:

  • Epoch Time
  • Years
  • Months
  • Days
  • Hours
  • Minutes

2. Set Up the Date Picker Elements

  1. Open the Date Picker Properties panel.
  2. Go to the Time tab.
  3. Enable the Time Field option.
  4. Set the Time Format to “24 Hour”.
form-builder-date-picker-time-format-min.png

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.

  1. Open the Form Calculation’s Widget Settings panel.
  2. Click the ellipsis icon.
  3. Go to the Options tab.
  4. Enable the Result field is read only option.
  5. Click Back.
  6. Finally, click the Save button to save the changes.
form-builder-calculation-widget-read-only-min.png

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.

We’ll be using the following dates as examples:

  • Date1 = Jan 01, 2020 at 01:00
  • Date2 = Feb 10, 2030 at 02:30

The actual difference between the two dates is 10 years, 1 month, 10 days, 1 hour, and 30 minutes.

Epoch Time Difference Formula

abs(Date1 - Date2) * 86400

Multiply the two dates’ absolute difference by 86400 to get the Epoch Time in seconds – using the example dates above, is 319080600.

Years Formula

floor(EpochTime / 31556926)

This one’s straightforward, just divide the Epoch Time by 31556926 then round down the quotient.

= floor(319080600 / 31556926)
= floor(10.11127)
= 10

Months Formula

floor((EpochTime % 31556926) / 2629743)

Get the remainder for years with “EpochTime % 31556926”, divide the result by 2629743, then round down the quotient to get the number of months.

= floor((319080600 % 31556926) / 2629743)
= floor(3511340 / 2629743)
= floor(1.33524)
= 1

Days Formula

floor(((EpochTime % 31556926) % 2629743) / 86400)

Get the remainder for years with “EpochTime % 31556926”, then get the remainder for months from the result with “{remainder for years} % 2629743”, divide the remainder for months by 86400, and finally, round down the quotient to get the number of days.

= floor(((319080600 % 31556926) % 2629743) / 86400)
= floor((3511340 % 2629743) / 86400)
= floor(881597 / 86400)
= floor(10.20367)
= 10

Hours Formula

floor((EpochTime % 86400) / 3600)

Get the remainder for hours with “EpochTime % 86400”, divide the result by 3600, then round down the quotient to get the number of hours.

= floor((319080600 % 86400) / 3600)
= floor(5400 / 3600)
= floor(1.5)
= 1

Minutes Formula

floor(((EpochTime % 86400) % 3600) / 60)

You can also use round() this time instead of floor(). The higher units of time require 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.

= floor(((319080600 % 86400) % 3600) / 60)
= floor((5400 % 3600) / 60)
= floor(1800 / 60)
= floor(30)
= 30

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(EpochTime / 31556926)

Months:

floor(EpochTime / 2629743)

Days:

floor(EpochTime / 86400)

Hours:

floor(EpochTime / 3600)

Minutes:

floor(EpochTime / 60)

Yes, it may look confusing at first but once you get the hang of it, especially the part about 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 guides: 

Comments and suggestions are welcome below. If you have a question, post it in our Support Forum so we can assist you.

Was this guide helpful?
Contact Support:

Our customer support team is available 24/7 and our average response time is between one to two hours.
Our team can be contacted via:

Support Forum: https://www.jotform.com/answers/

Contact Jotform Support: https://www.jotform.com/contact/

Send Comment:

Jotform Avatar
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Comments: