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

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History