Mastering Date and Time Calculation

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'.

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.4291666667OR3 R. 1545

And to get the result of 25 minutes:

1545/60=25.75OR25 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).

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:

- 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.