Help

DATETIME_DIFF result in Days and hours DD:HH?

Topic Labels: Formulas
Solved
Jump to Solution
1680 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Szymon_Hajduk
4 - Data Explorer
4 - Data Explorer

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 ?

1 Solution

Accepted Solutions
Claudio
6 - Interface Innovator
6 - Interface Innovator

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.

See Solution in Thread

1 Reply 1
Claudio
6 - Interface Innovator
6 - Interface Innovator

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.