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

Epoch Converter – What is epoch time?Unix epoch(orUnix timeorPOSIX timeorUnix 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 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.

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

- Open the
**Date Picker Properties**panel. - Go to the
**Time**tab. - Enable the
**Time Field**option. - 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.

- Open the Form Calculation’s
**Widget Settings**panel. - Click the
**ellipsis icon**. - Go to the
**Options**tab. - Enable the
**Result field is read only**option. - Click
**Back**. - Finally, click the
**Save**button to save the changes.

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

## Send Comment:

## 14 Comments:

It is easy to get an approximation when it comes to number of months and years in elapsed time between two dates, much more complicated to get it very precise. No, you cannot just subtract two dates for months and years worth of duration. I have got a shell script (works with Ksh, Bash), which does the calculation based on `Calendrical calculation, Dershowitz and Reingold, 1990'. That is at my gitHub at: github [dot] com/mountaineerbr/scripts

Is there a way to calculate the number of workdays between two dates?

How can I add a calculation results in time range field (from, to)

Can I simply just convert my date into Julian format so that I can just do a simple subtraction to find out how many days have passed? This is so complicated.

I need to auto-fill time in a column while adding 15 mins. So when 1st row is entered with time in decimal (ex. 2.57) second and rest will auto-fill the entire column.

1. 2.57

2. 3.12

3. 3.27

4. 3.42

etc.

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