Forum Discussion
Calculating actual paid hours of work
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)
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 Replies
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:
=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
