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

[Jennyb_Badass]

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. 

[kuovonne]

---

@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]

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

[Jennyb_Badass]

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]

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'))