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

Do you have an email address that I can send you what I built?

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 PM

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

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History