Excel Time Calculations
Posted by Frank Johnson -Leeds, England on December 06, 2001 7:56 AM
24/11/1997
Lunch out Lunch In Regular Hours: 8
12:30:00 PM 1:30:00 PM
TUE WED THU FRI
Nov 20 Nov 21 Nov 22 Nov 23
Start 8:30 AM 6:45:00 AM 8:30:00 AM 6:30:00 AM
Finish 5:30 PM 5:30:00 PM 5:57:00 PM 5:30:00 PM
Hours Worked 8:00 9:45 8:27 10:00
Regular hours 7:00 8:00 8:00 8:00
Overtime 1:00 1:45 0:27 2:00
I am trying to devise a simple daily employee time sheet that calculates
regular hours/mins worked between 8:30am and 5:30pm plus an overtime calculation
(again in hours and mins) for time worked before 8:30am and after 5:30pm.
Can anyone provide me the formula? Many thanks in frustrated anticipation.
Posted by IML on December 06, 2001 1:38 PM
I assumed Start time in B1, lunch out B2, lunch in B3, and out B4.
Total hours:
=+B4-B1-(B3-B2)
Overtime hours as you defined them
=(B1<0.354167)*(0.354167-B1)+(B4>0.729167)*(B4-0.729167)
Regular hours are total hours minus ot hours.
Be sure to format all to time.
Good luck
Posted by Frank Johnson on December 06, 2001 11:12 PM
To IML - many thanks your solution was spot on!
