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

Report •


#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.25

Then 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 PM

Adding the date as i have above should solve the problem with shifts going past midnight.


Report •

Related Solutions


Ask Question