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.

 

Thanks!

2 Replies

@LindsayD513 

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.

@LindsayD513 

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

image.png