Dates and time

Dates drive many of the questions a business asks every day: what is overdue, how many days late a payment is, when a follow-up is due, which month a sale belongs to. A Date and time field holds a date, with or without a time of day (its Show Time setting). This page shows what a formula can do with one.

Today

TODAY() is today's date, without a time. It changes by itself every day, so a formula that uses it is always current: "Due Date" < TODAY() is true for every row whose due date has passed.

A date plus or minus a number of days

Add or take away a number to move a date by that many days. The answer is a date.

• "Ordered on" + 30 is 30 days after the order date, for a payment due date.
• TODAY() - 30 is the date 30 days ago.
• 14 + "Start date" works too: the number can come first in an addition.

The days between two dates

Take one date away from another to count the whole days between them. The answer is a number, negative when the first date is the earlier one.

• TODAY() - "Due Date" is how many days late each row is.
• "Delivered on" - "Ordered on" is how long each delivery took.
• CASE WHEN Paid THEN 0 ELSE TODAY() - "Due Date" END counts the days late only for what is still unpaid.

Days are counted when at least one of the two is a date without a time; TODAY() is one. A view filtered on such a formula, for example Days late greater than 30, keeps itself current as the days pass.

The formula box does not work out what kind of value a date calculation gives, so the line under the box stays empty. Set it yourself under Display as: Number with 0 decimal places for a count of days, DateTime with Show Time off for a date plus days.

Parts of a date

• YEAR("Ordered on") gives the year: 2026.
• MONTH("Ordered on") gives the month, from 1 to 12.
• DAY("Ordered on") gives the day of the month, from 1 to 31.

To put dates into periods, use DATE_TRUNC. It gives the first day of the period a date falls in: DATE_TRUNC('month', "Ordered on") turns 17 March 2026 into 1 March 2026. The periods are 'year', 'quarter', 'month', 'week' and 'day'; a week starts on Monday.

Prefer DATE_TRUNC to MONTH when you sort or compare by period: MONTH gives 1 for January 2025 and for January 2026 alike, while DATE_TRUNC('month', …) keeps them apart.

Dates kept as text

Date functions need a date. When an import or a connected system has left dates in a text field, read them with TO_DATETIME first: YEAR(TO_DATETIME("Date as text")). Text written as 2026-10-29 is read; text that cannot be read as a date gives an empty value, so check a few rows.

Times of day

For a field that shows the time, the year, month, day and periods are worked out in UTC (Coordinated Universal Time). In Romania, 00:30 on 1 March in winter is 22:30 on 28 February in UTC, so DAY gives 28 for it.

Limits: two fields that both show a time of day are not counted in days when you take one from the other. No function adds months or years, gives the day of the week, or gives the hour or the minute, and DATE_TRUNC stops at a day.

Summaries can also put dates into columns by year, quarter, month, week or day without a formula: see Views.