Help

Date Formula | Date Difference (End-Start Date w/Time) But if No Date Return 0 not NAN

Topic Labels: Formulas
540 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Jennyb_Badass
4 - Data Explorer
4 - Data Explorer

Current Formula: 

DATETIME_DIFF({End Time},{Start Time},'hours')
 
But if I have a "Day-Off", no date + time are listed, so on these line items they return NAN. 
If start + end date are blank, return 0 (Zero).
 
Searching the community post, I have not been able to locate this formula. I would appreciate your help. 
4 Replies 4

@Jennyb_Badass wrote:
I have not been able to locate this formula

You need to combine different formula functions together to get the effect you want. Mixing and matching different formula functions together is a key aspect of the Airtable formula language. Try this.

IF(
  AND({End Time}, {Start Time}),
  DATETIME_DIFF({End Time}, {Start Time}, 'hours'),
  0
)

 

Jennyb_Badass
4 - Data Explorer
4 - Data Explorer

Thank you.  This formula is what I was looking for. Appreciate your help.

Hi Kuovonne, This formula kind of works. It's rounding up the hours. If I have someone working 8:15am - 3:00pm, it returns 6 hours. Not 5.45. Is it possible to get the exact hours and minutes in the formula? 

Jennyb_Badass
4 - Data Explorer
4 - Data Explorer

Can you help with calculating the minutes? 

Start Time: 7/26/2023 6:15pm            End Time:  7/26/2023 7:30pm 

Formula returns 1 Hour 

Formula Using: 

IF({Start Time}= BLANK(), 0,DATETIME_DIFF({End Time}, {Start Time}, 'hours'))