Skip to main content

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

  • July 4, 2023
  • 4 replies
  • 33 views
J
Forum|alt.badge.img+2

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

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • July 4, 2023
@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 )

 

J
Forum|alt.badge.img+2
  • Jennyb_Badass
  • Author
  • New Participant
  • July 7, 2023

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

J
Forum|alt.badge.img+2
  • Jennyb_Badass
  • Author
  • New Participant
  • July 24, 2023
@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 )

 


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? 

J
Forum|alt.badge.img+2
  • Jennyb_Badass
  • Author
  • New Participant
  • August 1, 2023

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'))
Terms & ConditionsAccessibility statement