Calculating Overtime Daily after 40 hours worked in a week

SundanceStriping · Jul 23, 2014

I am at a loss. I have tried for four days now to figure out a formula to calculate regular hours, overtime hours and total hours per day and per week.

My current time card is as follows:

B13-B20 = Sun (noon until) -Sun (until noon)
C-L = in & out times for all days of the week above
M13-N20 = regular hours
N13-N20 = overtime hours
O13-O20 = Total Hours

Also:

M21 = total weekly regular hours
N21 = total weekly overtime hours
o21 = total weekly hours

I have already formatted the in & out times, regular, overtime and hour times to show/calculate correctly according to time/decimal conversion.

My main issue is that I want the daily time to show in regular hour until 40 hours for the week has been met. Once met, I would like the hours for the day to be shown in the overtime column.

Please help! Thanks

AhoyNC · Jul 25, 2014

Re: Overtime Calculation

Based on your first post, see if this works (it should at least give you a start). I added a cum column to your sheet.
Note that in Excel time is a fraction of a 24 hour day. That's where the 40/24 comes from in the formulas.
I also formatted the calculations as [h]:mm

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Day

In

Out

In

Out

In

Out

In

Out

In

Out

Regular Hrs.

Overtime Hours

Total

Cum Column

2

Sunday Noon Until

8:00 AM

11:00 AM

3:00

3

Monday

8:00 AM

11:00 AM

12:00 PM

6:00 PM

9:00

12:00

9:00

4

Tuesday

8:00 AM

11:00 AM

3:00

15:00

3:00

5

Wednesday

8:00 AM

11:00 AM

3:00

18:00

3:00

6

Thursday

8:00 AM

11:00 AM

3:00

21:00

3:00

7

Friday

8:00 AM

11:00 AM

3:00

24:00

3:00

8

Saturday

8:00 AM

11:00 AM

12:00 PM

6:00 PM

8:00 AM

11:00 AM

12:00

36:00

12:00

9

Sunday Til Noon

8:00 AM

11:00 AM

12:00 PM

6:00 PM

4:00

5:00

45:00

9:00

10

Total Hrs.

40:00

5:00

Sheet

SundanceStriping · Jul 25, 2014

Re: Overtime Calculation

Thanks. This worked great except for one thing. It may just be that it's not possible to do it.

Once 40 hours were hit, the appropriate time began to show up in the overtime column yet the regular hours column still had those hours noted as well.

I would like for it to look something like this.

Reg. OV Total Cum.
12 0 12 12
9 0 21 9
9 0 30 9
9 0 39 9
1 11 51 12 (note:from here forward with current formulas the Reg. shows 12, OV 11, Total 51, Cum, 12)
0 12 63 12

Is it possible to be done this way?

Joe4 · Jul 25, 2014

Re: Overtime Calculation

I merged your two threads together (and deleted the references to duplicate threads).

In the future, please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).

jasonb75 · Jul 25, 2014

Re: Overtime Calculation

SundanceStriping said:
Thanks. This worked great except for one thing. It may just be that it's not possible to do it.

Once 40 hours were hit, the appropriate time began to show up in the overtime column yet the regular hours column still had those hours noted as well.

Who's suggestion did you try? As far as I can see, my suggestion does exactly what you asked for.

SundanceStriping · Jul 25, 2014

Re: Overtime Calculation

Thanks and sorry for the misunderstanding.

SundanceStriping · Jul 25, 2014

Re: Overtime Calculation

I tried yours but nothing was showing up in overtime. I am attempting now to start back over from scratch now.

AhoyNC : worked great as well except that once overtime kicked in, the total hours for that day was still showing up in regular but would have the correct hours in regular.