Types / Dates and Durations

Dates are a key part of your data, and there's a couple of important things you should know when using them in formula.

1. Dates are just numbers

Dates are represented as a number of milliseconds from 01/01/1970 00:00 UTC (because that's when computers started counting). Dates always include both a date and a time (down to the number milliseconds).

Here are some dates and their equivalent numbers:

01/01/1900 00:00 UTC  = -2208988800000
01/01/1970 00:00 UTC  = 0
12/03/2019 14:00 UTC  = 1552399200000

As dates are just numbers you can easily compare two dates (the higher the number the later the date):

johns_birthday > janes_birthday

Because working out the correct number for a date would be difficult, there's a Date function to help with creating a new date (and time).

Date(2019, 3, 12) // 12/03/2019 00:00 UTC
Date(2019, 3, 12, 14, 12) // 12/03/2019 14:12 UTC

If you have a date and time, but you really just want to compare days (and not consider the time aspect), then you can reset both dates to the start of the day.

StartOf(date1) - StartOf(date2) = exactly duration in days

Numbers are useful for working and manipulating dates, but most people won't understand a date represented as a number! For that, we can convert our date into a string format format.

FormatDate(Date(2019, 1, 1), 'DD/MM/YYYY') // "01/01/2019"

There are a number of other functions for dates - check out the full dates reference.

2. Durations are also numbers

A duration is stored in milliseconds - that enables you to be very precise about the how much time has passed.

1 second = 1000
1 minute = 60000
1 hour = 3600000
1 day = 86400000

Storing durations as a number allows you to easily perform basic math on them. For example, if you wanted to add two durations together, you can simply use:

duration1 + duration2

As dates are also numbers, you can also easily add a duration to a date:

date_of_purchase + duration_of_warranty

As these numbers can get quite large, there are a number of formula that make creating and using durations easier.

The following formula can be used to quickly create a duration (it's still just a number, but using these functions mean you don't have to calculate the number yourself):

Days(1) 	is equivalent to 86400000
Hours(2) 	is equivalent to 7200000
Mins(15) 	is equivalent to 900000
Seconds(10)	is equivalent to 10000

If you need to get the number of days, hours or mins back from a duration you can use:

NumberOfDays(86400000)		= 1 day 
NumberOfHours(3600000)		= 1 hour
NumberOfMins(60000)		= 1 minute
NumberOfSeconds(10000)		= 10 seconds

There are a number of other functions for durations - check out the full duration reference.

3. Dates are always stored in UTC

Working with timezones can be difficult, so to keep things consistent we always store dates in the UTC timezone (as it never changes). If you want to output a time for a user in a particular timezone, you can use the timezone option in the FormatDate function.

FormatDate(date_1, "MM:SS", "America/Los_Angeles")
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.