# Timesheet in Excel 2003

May 21, 2009 at 08:53:29
Specs: Windows XP
 I have a two week period time sheet. I need to calculate time worked for each day with a maximum of 40 hours per week, the rest being overtime. My Problem is there are two shifts per day and some shifts finish after midnight, say 01H00. I can't get the proper totals when work ends after midnight. The formula I am using is;=IF((((D20-C20)+(F20-E20))*24)>8;8;((D20-C20)+(F20-E20))*24)Can anyone help me?Thank you so much...

See More: Timesheet in Excel 2003

#1
May 21, 2009 at 10:37:08
 Do you have an email address that I can send you what I built?

Report •

#2
May 21, 2009 at 10:47:50
 Columns A=Day B=IN C=OUT D=IN E=OUT F, G, H are the forumla cells listed below.I have mine set up as above. Day being mon, tues, wed....then in col F i have formula: =ROUND((((E3-B3) - ( D3-C3))*24)/0.25,0)*0.25Then in F10 I do a sum of all the hours in F.In G10 I calculate the regular hours by using: =IF(SUM(F3:F9)>40,40,SUM(F3:F9))In H10 I calculate the Overtime Hours by using: =IF((SUM(F3:F9))>40,(SUM(F3:F9))-40,0)the format on the cells where time is entered is this time format: 4/28/2009 12:30:00 PMAdding the date as i have above should solve the problem with shifts going past midnight.

Report •

Related Solutions