How to subtract a lunch break from daily hours

[Jay108]

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!

[Jay108]

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?

[Sho]

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

[Jay108]

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!!