Jan 16 2022 07:37 AM
I'm setting up a spreadsheet to display a work schedule at my part-time restaurant job. The majority of it is finished, but I forgot about having the cells calculate hours for shifts worked that are 6 hours or more that need a half hour break.
For instance, if I am working a shift that runs 3:00pm to 11pm, I would receive a half hour break, and my total hours for the shift should be 7.5, not 8.
I currently have the cell to display the total hours for the shift set up as (=Cell-Cell). (I'm using drop down lists to pick the start and end times of the shift).
Is this something that's possible to set up? I'm not super familiar with more in-depth formulas in Excel, I set this up as a test for my manager.
Thanks!
Jan 16 2022 08:19 AM
Let's say start time is in B2 and end time in C2.
=24*MOD(C2-B2,1)-(24*MOD(C2-B2,1)>=6)/2
This will also work if you start before midnight and end after midnight.
The formula can be filled down.
Jan 16 2022 08:33 AM
It depends also you'd like to show shift time as the time or calculate number of hours in the shift. In Excel 1 hour is actually decimal number which is equal to 1/24. What we show as 01:00 is only human friendly notation. Variants are