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
| Formula | Result |
|---|---|
| Date − Date | Duration, in whole calendar days |
| Date and time − Date and time | Duration, the real time between them |
| Date + Duration, Date − Duration | Date (the duration must be whole days) |
| Date and time + Duration | Date and time |
| Date + number, Date − number | Date, moved by that many days |
| Duration + Duration, Duration − Duration | Duration |
| Duration × number, Duration ÷ number | Duration |
| Duration ÷ Duration | number |
<, <=, >, >=, == between two dates, two date-and-times or two durations | true 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, whileDays(1)is exactly 24 hours.
See the reference for all date and duration functions.