If/Then Forumla to subtract hours

Occasional Visitor

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.



2 Replies


Let's say start time is in B2 and end time in C2.




This will also work if you start before midnight and end after midnight.

The formula can be filled down.


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