Skip to main content

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?


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🇲🇲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 ?


Reply