Forum Discussion

LindsayD513's avatar
LindsayD513
Copper Contributor
Jan 16, 2022

If/Then Forumla to subtract hours

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    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

  • 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.

Resources