Skip to Content
FormulasDates and durations

Dates, durations and time zones

Dates, times and lengths of time are values that formulas understand, so you can add and subtract them directly. Cells in Date, Date and time and Duration columns arrive in a formula already as dates and durations, with no conversion needed.

An example: when does a task end?

A table has a Start column of type Date and a Length column of type Duration. A Formula column named End works out the end date:

CurrentRow("Start") + CurrentRow("Length")

With Start = 01/31/2024 and Length = 2d (shown as 2 days) that gives 02/02/2024: a date plus a duration is a date. The result is shown in the same format as the Start column, so it matches the other dates in your table. A Date moves by whole days only: adding a duration such as 1 day 3 hrs to a Date is an error that asks for a Date and time instead.

A formula column can use the result of another formula column, wherever it is in the table, and gets it as a date, not as text. To see how long is left, add a column that subtracts today’s date from End. A date minus a date is a duration:

CurrentRow("End") - Today()

This shows 40 days for a task that ends in 40 days and -3 days for one that ended three days ago. To print it as 3 days ago, use FormatDuration with the "ago" style:

FormatDuration(CurrentRow("End") - Today(), "days", 2, "ago")

Formula columns cannot depend on each other in a circle: if A uses B and B uses A, both show an error that names the loop.

What combines with what

FormulaResult
Date − DateDuration, in whole calendar days
Date and time − Date and timeDuration, the real time between them
Date + Duration, Date − DurationDate (the duration must be whole days)
Date and time + DurationDate and time
Date + number, Date − numberDate, moved by that many days
Duration + Duration, Duration − DurationDuration
Duration × number, Duration ÷ numberDuration
Duration ÷ Durationnumber
<, <=, >, >=, == between two dates, two date-and-times or two durationstrue or false

Mixing other kinds, such as adding two dates or a date and a number of hours, is an error that says what it found. A Date has no time of day, so for hours and minutes use a Date and time (and ToDateTime turns a Date into midnight).

For text that is not in a Date, Date and time or Duration column, convert it first with ToDate, ToDateTime or ToDuration.

When a cell holds something that is not a date, for example a word typed into a Date column, the formula reports an error instead of treating the cell as empty.

Time zones

A Date is a calendar day and has no time zone. A Date and time is the time shown on the clock in your Organization’s time zone, which you set on the Organizations page. Today() and Now() follow that zone, so a task due “today” is the same day for everyone in the Organization. Changing the time zone does not change any stored value; only formulas that use the current date or time give a different result.

The time zone also decides what happens across daylight saving changes:

  • The difference between two dates is counted in calendar days.
  • The difference between two dates with times is the real time passed, so a day with a clock change is 23 or 25 hours.
  • DateAdd(date, 1, "days") moves by a calendar day, while Days(1) is exactly 24 hours.

See the reference for all date and duration functions.

Last updated on