dateDiff returns the number of days between two dates, or the number of another period if you name one. Days, hours and minutes are unambiguous. Months and years are not, and for ages, tenures and contract lengths it is safer to compute complete periods yourself.

Copy and paste

Days from order to shipment:

dateDiff({Order Date}, {Ship Date})

Calendar days, ignoring the time of day:

dateDiff(truncDate('DD', {Order Date}), truncDate('DD', {Ship Date}), 'DD')

Hours or minutes:

dateDiff({Created At}, {Resolved At}, 'HH')

dateDiff({Created At}, {Resolved At}, 'MI')

Days open so far, for rows without an end date:

dateDiff({Order Date}, coalesce({Ship Date}, now()))

Complete years (age, tenure):

extract('YYYY', {End Date}) - extract('YYYY', {Start Date})
- ifelse(
    extract('MM', {End Date}) * 100 + extract('DD', {End Date})
      < extract('MM', {Start Date}) * 100 + extract('DD', {Start Date}),
    1, 0)

Complete months:

(extract('YYYY', {End Date}) - extract('YYYY', {Start Date})) * 12
+ extract('MM', {End Date}) - extract('MM', {Start Date})
- ifelse(extract('DD', {End Date}) < extract('DD', {Start Date}), 1, 0)

Order of the arguments

dateDiff(date1, date2) returns date2 minus date1: put the earlier date first. Reversed, the result is negative, which is useful for “days until due” but a common source of negative lead times.

The optional third argument is a period: 'DD' (the default), 'WK', 'MM', 'Q', 'YYYY', 'HH', 'MI', 'SS' or 'MS'.

Days and times

When the fields hold times, a difference in days can depend on the hours: an order at 23:00 shipped at 01:00 the next morning is one calendar day later but only two hours later. If the report counts calendar days, truncate both sides with truncDate('DD', ...) as in the second formula, and the time of day no longer matters.

Months and years

With 'MM' or 'YYYY', dateDiff returns a whole number, and the documentation does not say how it treats incomplete periods. A user on the QuickSight community forum compared 13 December 1986 with 18 October 2021: QuickSight returned 35 years where Athena returned 34, although only 34 complete years have passed. For ages and tenures, use the complete-period formulas above:

Start Date End Date Days Complete months Complete years
1986-12-13 2021-10-18 12,728 418 34
2025-06-15 2026-06-14 364 11 0
2025-06-15 2026-06-15 365 12 1
2026-01-31 2026-02-28 28 0 0
2024-02-29 2025-02-28 365 11 0
2024-02-29 2025-03-01 366 12 1

The complete-years formula compares month and day as one number, MMDD: the year counts only once the end date has reached the anniversary. Someone born on 29 February turns a year older on 1 March in a non-leap year. Both formulas expect the end date to be on or after the start date.

Null dates

If either date is null, dateDiff returns null, and the row drops out of averages. That is usually right for open orders. When you want “days open so far”, replace the missing end date with now(), as in the fourth formula, and keep in mind that now() changes every time the dashboard loads.

Working days

dateDiff counts every calendar day. For Monday to Friday only, see how to count working days between two dates.

Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.

References