Skip to main content

How to subtract a lunch break from daily hours

  • August 22, 2023
  • 3 replies
  • 35 views
J
Forum|alt.badge.img+3
  • Jay108
  • New Participant
  • 3 replies

Hi all,

 

I'm trying to create a formula to add on to a scheduling template I'm using. There is already a field for Monday hrs, Tuesday Hrs, etc. Formatted like this: 

IF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'hours'))
 
I'd like to add a new field "Pay Hours", which stipulates that if the field "Monday Hrs" has a value of 6 or higher, it should subtract 0.5 hours, so that the column under Pay Hours shows that day's hours minus 0.5.  How do I write that formula? I've tried a few times with no luck. 
 
Thanks in advance!

3 replies

J
Forum|alt.badge.img+3
  • Jay108
  • Author
  • New Participant
  • 3 replies
  • August 22, 2023

And actually, upon more investigation, the above mentioned formula for the total daily hours is faulty - It can't divide into half hours; for some reason it defaults to round down to the closest hour, which is problematic. Surprising for a template that airtable provides. Anyone know how to fix this?

 

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • 560 replies
  • August 22, 2023

Hi @Jay108 ,

For example, something like this

IF({Monday Start} = BLANK(), 0, IF(DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60 > 5, DATETIME_DIFF({Monday End}, DATEADD({Monday Start}, 30, "minutes"), 'minutes')/60, DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60) )

 

J
Forum|alt.badge.img+3
  • Jay108
  • Author
  • New Participant
  • 3 replies
  • August 23, 2023

Hi @Jay108 ,

For example, something like this

IF({Monday Start} = BLANK(), 0, IF(DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60 > 5, DATETIME_DIFF({Monday End}, DATEADD({Monday Start}, 30, "minutes"), 'minutes')/60, DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes')/60) )

 


Thanks so much @Sho 

Using the minutes / 60 worked great for getting the correct values. The above formula didn't work, but I just used this in the regular "Monday Hrs" field and it corrected the values: 

IF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes'))/60

Now, to deduct the half hour lunch, I created a separate field "Pay Hours" and I tried this: 

IF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes'))/60-0.5
 
It worked on all of the cells that had values, but strangely, even if the "monday start" was blank, any of the cells with 0, got changed to -0.5. And the other problem I realized was that shorter shifts, under 5 hours, should not have the 0.5hr deducted. 
So maybe I can kill 2 birds with one stone if I can manage to use a formula that says any value under 5 does not have the 0.5hr deducted. Is that an easy thing to do?
 
Could I somehow use this formula: IF({Monday Start} = BLANK(), 0,DATETIME_DIFF({Monday End}, {Monday Start}, 'minutes'))/60-0.5  but tell it to not subtract the 0.5 from any result less than 5?
 
Thanks in advance for your help!!


Terms & ConditionsAccessibility statement