DATETIME_DIFF result in Days and hours DD:HH?

[Szymon_Hajduk]

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 ?

[Claudio · Accepted solution]

Szymon, cześć! :raised_hand_with_fingers_splayed:

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.