CODEWITHSUNDEEP
excel
Binit Patel
Binit Patel

Reputation: 21

how to calculate overtime in excel using by weekly pay?

+-----------+-----------+------+
|    Day    | Reg.Hours |  OT  |
+-----------+-----------+------+
| Monday    | 8         | 0.75 |
| Tuesday   | 8         | 0.5  |
| Wednesday | 8         | 1    |
| Thursday  | 8         | 0    |
| Friday    | 8         | 2.25 |
| Saturday  | 0         | 0    |
| Sunday    | 0         | 0    |
| Monday    | 8         | 0    |
| Tuesday   | 5.5       | 0    |
| Wednesday | 8         | 3.25 |
| Thursday  | 8         | 2.75 |
| Friday    | 8         | 0.5  |
| Saturday  | 0         | 0    |
| Sunday    | 0         | 0    |
+-----------+-----------+------+

Rules are:

Monday to Sunday, at least, work 40 Reg.Hours to get any overtime for that week.

In the above data set for the 2nd week Monday to Sunday for Reg.Hours total is 37.5 that means, the company will deduct 2.5 OT hours from the 2nd week of Monday to Sunday OT hours.

How do I calculate in Excel with a formula for calculating both weeks OT in 1 excel formula?

Let's say per hour pay is $30.00

Upvotes: 0

Views: 1114

Answers (3)

TAM
TAM

Reputation: 11

Assuming Day is Column A, Row 1, Regular is Column B, Row 1, and OT is Column C, Row 1:

First, Calculate total regular and OT hours for each week in the pay period:

                                        Col. A   Col. B        Col. C
On Row 17, Calculate Regular/OT Hours:  Week 1   =SUM(B2:B8)   =SUM(B9:C15)
On Row 18, Calculate Regular/OT Hours:  Week 2   =SUM(C2:C8)   =SUM(C9:C15)

On Row 19, Calculate:

  Regular Hours   =IF(B17+C17>=40,40,B17+C17)+IF(B18+C18>=40,40,B18+C18)
  OT Hours        =IF(B17+C17>=40,B17-40+C17,0)+IF(B18+C18>=40,B18-40+C18,0)

Hope this helps.

Upvotes: 0

Ji_in_coding
Ji_in_coding

Reputation: 1701

I think the easiest way would be to use a iamge to demonstrate the answer

Numbers View

Formula View

Upvotes: 0

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

You could do this with an Array Formula, but as you only have the days of the week, not the actual dates (which are unique) in your table, I think this will be easier with a helper column.

Assuming the first 3 columns are A, B, and C I would add a helper column in column D, starting with D2 and copied down as follows:

=IF(A2="Sunday",MAX(0,SUM(OFFSET(B2,-6,0,7,2))-40),"")

What this does is: Once a week (on Sunday, at the end of the week), Excel will sum up the previous 7 days of data, including column B & C. This is the total number of hours worked. That amount, minus 40 [limited to 0, if < 40 hours are worked], represents the total number of OT hours worked that week.

The fact that you have a regular hours column and an OT column is a bit of a red herring - instead of checking whether any OT was worked on any day, and then subtracting by the number of regular hours NOT worked on other days, just compare the whole week's work to 40 and end the calculation there.

Upvotes: 1

Related Questions