Calculating actual paid hours of work

[KC060194]

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)

[#2 · Accepted solution]

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

[#3]

It worked! Thank you

[#4]

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

=C1-(TIME(0,35,0)*(C1>5/24))

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