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

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

Check out this screenshot:

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.

Use DATETIME_DIFF function

Hi. Has anyone managed to work out how to calculate total number of hours worked between two dates (multiple weeks sometimes) but only including working hours e.g. Mon-Fri, 0900-1700 ?