Calculating actual paid hours of work

New Contributor

I have a spreadsheet in military time Start A1 Finish B1 and then the hours (Cell C1) worked is SUM(B1-A1). Now if the hours worked is greater than 5 hours there is a required unpaid 35-minute break period. So to calculate actual paid hours I made this formula, but it doesn't seem to work. Help!


=IF(SUM(B1-A1)>5, (SUM(B1-A1)-TIME(0,35,0), SUM(B1-A1)

3 Replies
best response confirmed by KC060194 (New Contributor)

@KC060194 Excel stores times as part of a full day of 24 hours. So a duration of 5 hour equals a numerical value of 5/24. So change >5 by >5/24 and see if that works.

It worked! Thank you

@KC060194 Glad I could help. Perhaps you want to consider this formula:



No need for the SUM in C1, by the way. B1-A1 will do. And no need for IF or repeating B1-A1