Skip to main content

Hi I need to get results in days : hours bettwen to dates.

I have {date start} , {date stop}

and when {date stop} fincion should count to Now()


Funcion

IF({date stop}="",DATETIME_DIFF(NOW(),{date start},‘hours’)/24,DATETIME_DIFF(date stop},{date start},‘hours’)/24)

It shows results in decimal how many days but now in DD:HH


I tryed:

DATETIME_FORMAT( IF({date stop}="",DATETIME_DIFF(NOW(),{date start},‘hours’)/24,DATETIME_DIFF({date stop},{date start},‘hours’)/24),‘DD:HH’)

but I get ERROR


Any advice ?

Szymon, cześć! 🖐


First step is to get the date/time difference in days and hours.

You can use this:

DATETIME_DIFF({date stop},{Date start},'days') &':'& MOD(DATETIME_DIFF({date stop},{date start},'hours'),24)

The formula calculates the number of days and the remainder (modulus) in hours.

(Perhaps there’s a simpler formula based on another date/time function.)


Then, it looks like you want one of two possible results, depending on whether {date stop} is blank or not. (It seems that {date start} will never be blank.)

In an Airtable formula, you can use IF({Field Name} at the beginning of the IF statement to evaluate whether the field is blank.


So, you’ll get the long formula:

IF({date stop}, DATETIME_DIFF({date stop},{date start},'d') &':'& MOD(DATETIME_DIFF({date stop}{date start},'h'),24), DATETIME_DIFF(NOW(),{date start},'d') &':'& MOD(DATETIME_DIFF(NOW(),{date start},'h'),24))


(You can use ‘d’ and ‘h’ instead of ‘days’ and ‘hours’. See Supported unit specifiers for DATETIME_DIFF)


BTW, you can format a formula as Duration with various formats but not DD:HH.


Actually, after all that, I realize that my formula will not give put a leading zero if the number of days or hours is less than 10. Hope that’s okay.

Reply


Terms & Conditions