Why DATETIME_DIFF Shows the Wrong Value in Airtable (And How to Fix It)

If Airtable shows 0:02 when you expected 2:00, the formula is probably returning minutes while the field is displaying them as seconds. The same kind of mismatch can make a result look negative, one minute short, or blank.

The fix depends on what you want to see: a duration, a number of minutes, decimal hours, or working days. The examples below use Start Time and End Time fields.

When a two-hour difference shows as 0:02

This formula returns the difference in minutes:

DATETIME_DIFF({End Time}, {Start Time}, 'minutes')

For a two-hour gap, Airtable returns 120. If the formula field is formatted as Duration, Airtable reads 120 as 120 seconds, so the field shows 0:02.

To display the result as a duration, return seconds instead:

DATETIME_DIFF({End Time}, {Start Time}, 'seconds')

The result is now 7200, which Airtable displays as 2:00 when the field uses the Duration format.

If you want a plain number such as 120 minutes, keep the formula in minutes and format the formula field as Number, not Duration.

Airtable formula field showing a time difference in the wrong duration format

When the result is one minute short

DATETIME_DIFF returns whole units. It does not round the remaining seconds up.

For example, a difference of 1 hour, 59 minutes, and 50 seconds returns 119 when you ask for minutes. Calculate in seconds first, then round the result if that is what your workflow needs:

ROUND(DATETIME_DIFF({End Time}, {Start Time}, 'seconds') / 60)

For decimal hours, divide the number of minutes by 60 instead:

DATETIME_DIFF({End Time}, {Start Time}, 'minutes') / 60

That returns 1.5 for 90 minutes.

When the result is negative

The first date goes first in DATETIME_DIFF. Put the later date first when you want the elapsed time between two dates:

DATETIME_DIFF({End Date}, {Start Date}, 'days')

If Start Date is later than End Date, Airtable returns a negative number. Use ABS() only when the direction does not matter and you always want a positive result:

ABS(DATETIME_DIFF({End Date}, {Start Date}, 'days'))

When the result is blank or zero

Check that both fields contain dates or date-times. If a date came from an import, form, lookup, or integration as text, convert it before using it in the calculation:

DATETIME_DIFF(
  DATETIME_PARSE({End Date Text}),
  DATETIME_PARSE({Start Date Text}),
  'days'
)

If the fields are already date fields, open a record and check that neither value is empty. A missing date cannot produce the difference you expect.

When you need working days

Use DATETIME_DIFF for calendar time. It includes weekends.

For weekdays, use WORKDAY_DIFF instead:

WORKDAY_DIFF({Start Date}, {End Date})

You can pass a list of holiday dates as the third argument when those holidays should also be excluded.

When the result changes because of time zones

Time zones matter when the two fields include a time and the dates were entered or displayed in different zones. First check the time zone setting on both date fields and make sure they use the same setting.

Use SET_TIMEZONE() when you need to convert a date for display in another time zone. It is normally used with DATETIME_FORMAT(), which returns text, so it is not a drop-in replacement for a date value inside DATETIME_DIFF.

For most calculations, keeping both date fields on the same time zone setting is the simpler fix.

Quick check before changing the formula

Look at these three things first:

  • What should the result mean? Use seconds for a Duration field, minutes for a number of minutes, and minutes divided by 60 for decimal hours.
  • Are the dates in the right order? The later date should be first when you want a positive elapsed time.
  • Are both values real dates? Parse text dates with DATETIME_PARSE() before calculating the difference.

For a date window such as "within 30 days," see how to check if a date falls within 30 days of another date in Airtable. For a date difference used in a daily email workflow, see how to send automated follow-up emails in Airtable.