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

DATETIME_DIFF is one of the most useful formulas in Airtable and one of the most frustrating when the result looks wrong. The formula is almost always calculating correctly. The problem is nearly always either the field formatting or a misunderstanding of what the unit specifier controls.

Here are all the common ways DATETIME_DIFF produces unexpected results, and the fix for each.

Problem 1: The Field Shows 0:02 Instead of 2:00

This is the most common issue. You write a formula to track time between a start and end timestamp:

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

The formula preview shows 120, which is correct for a two-hour difference. But when you look at the field in the table it shows 0:02 instead of 2:00.

The cause: duration fields in Airtable always store and interpret values in seconds, regardless of the display format. When the field is formatted as Duration (h:mm), it reads 120 as 120 seconds, which is 0 hours and 2 minutes. Hence 0:02.

Fix option 1: Change the unit in the formula to seconds

Since duration fields store values in seconds, change the formula to output seconds instead of minutes:

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

Now 7,200 seconds (two hours) formatted as h:mm correctly shows as 2:00.

Fix option 2: Change the field format to Number

If you want to see the raw number of minutes rather than a formatted duration, change the field formatting from Duration to Number. Go to the field settings, click Formatting, and switch to Number. The field now shows 120 as a plain number representing 120 minutes.

Problem 2: The Unit Specifier Is Wrong or Unrecognised

The output defaults to seconds if the unit specifier is not recognised. If you are seeing a very large number, check whether the unit specifier is spelled correctly. Common mistakes:

• 'min' should be 'minutes' (or the short form 'm')
• 'hr' should be 'hours' (or 'h')
• 'day' should be 'days' (or 'd')

All valid unit specifiers confirmed from Airtable's official documentation: milliseconds, seconds, minutes, hours, days, weeks, months, quarters, years. Each has a single-letter or short abbreviation equivalent.

Problem 3: The Result Is Negative

DATETIME_DIFF returns a negative number when the first date argument is earlier than the second. The syntax is:

DATETIME_DIFF(later_date, earlier_date, 'unit')

If your start and end dates are in the wrong order, you get a negative result. Swap the arguments:

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

To always return a positive number regardless of order, wrap in ABS():

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

Problem 4: The Result Is Always 0 or Blank

This usually means one of the date fields is empty or not formatted as a date/time field. DATETIME_DIFF requires both arguments to be actual datetime values. If either field is empty, the result is 0 or blank.

If you are using a text field that contains date strings, convert it to a date value first using DATETIME_PARSE():

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

Problem 5: Whole Number When You Need a Decimal

DATETIME_DIFF returns whole numbers, truncated not rounded. For example, 1.5 hours returns 1 when the unit is hours, not 2.

If you need more precision, calculate in a smaller unit and divide:

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

This gives you hours as a decimal. 90 minutes becomes 1.5 rather than being truncated to 1.

Problem 6: Need Business Days Instead of Calendar Days

DATETIME_DIFF counts all calendar days including weekends. If you need working days only, use WORKDAY_DIFF() instead:

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

WORKDAY_DIFF excludes weekends automatically. You can also pass a list of holiday dates as a third argument to exclude those too.

Problem 7: Off-by-One Results Due to Timezones

Airtable stores all dates in GMT internally. If a team member's computer is in a different timezone, the same date field can appear as different calendar days depending on who is viewing it, which causes DATETIME_DIFF results to be off by one day for someone.

For precise timezone-aware calculations, use SET_TIMEZONE() to standardise the dates before passing them to DATETIME_DIFF.

Quick Reference

For more on working with date-based formulas and automations in Airtable, see How to Send Automated Follow-Up Emails in Airtable for how DATETIME_DIFF is used in real automation conditions, or How to Add Recurring Events to an Airtable Calendar for date arithmetic patterns.