Help

I would to calculate the difference between two dates with seconds not only hours and minutes

Topic Labels: Formulas
947 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Isaac_Massoud
4 - Data Explorer
4 - Data Explorer

Hello, I am new to airtable
My issue is I want to calulate the difference between two dates and calculate the seconds also, but i cant even add the seconds to the date how this could be calculated if it’s not even added.

Im using the Datediff formula, is there’s any other solution?
Screenshot 2022-08-27 10.29.10 AM

4 Replies 4

Hi @Isaac_Massoud, it’s possible, but in my experience somewhat tricky

Check out this screenshot:
Screenshot 2022-08-27 at 5.02.20 PM

Here’s a link to the base

You can duplicate the base to view all the formulas

I’ve noticed that, when trying to calculate the difference with seconds, it needs to be done between two fields that are formatted to display the seconds, and I’ve attached the formula I use to do so below. When I try to do this with formulas that aren’t already displaying the seconds the results are not what I expect.

DATETIME_FORMAT(
  {Date 1},
  "DD MMM YYYY hh:mm:ss"
)

Welcome to the Airtable community!

Airtable formulas are calculated based on how the underlying data is stored, not how it is displayed. And Airtable stores date/time values down to the millisecond, even if it does not normally show the milliseconds or let you enter seconds/milliseconds.

Here are the main ways to get date/time values with seconds:

  • use a created time or a last modified time field
  • use a formula that calculates a date/time
  • set the field value using code (scripting or REST api)

@Isaac_Massoud

If you’re okay with entering army time, you may also want to look into the duration field type.

It automatically stores all values in seconds, and you can visually display the field with seconds as well. And math is just a simple subtraction, too.

This field type would be the most useful for you if all of your dates are going to be the same (like your screenshot shows). Then, you can just have 1 date field and 2 duration fields.

Phil1
5 - Automation Enthusiast
5 - Automation Enthusiast

Use DATETIME_DIFF function