how to calculate overtime in excel using by weekly pay?

Question

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

It is very hard to tell what is causing the numbers there in the OT column - are you sure you are being consistent about what OT is, and what regular time is? For example, in my jurisdiction there are 2 ways to get OT pay: working more than 8 hours in a day, or working more than 40 hours in a week. So someone who works 10 hours on Monday and doesn't work the rest of the week, gets 2 hours of overtime pay (usually, for most wage jobs). — Grade 'Eh' Bacon
@Grade'Eh'Bacon Where I live it is only by week, if you work more than 40 during a week its is overtime. So one could work 4-10 hour days and no overtime. That being said =SUM(SUM($B$2:$C$8)-40,SUM($B$9:$C$15)-40) — Scott Craner
I believe the OP means 'bi-weekly' when saying 'by weekly'. It appears all hours within a 2 week span are 'flexed' and only hours over 80 are counted as 'OT'. — Kris B
To get OT you have to finish 40 hours a week. and for example, you work Monday to Thursday 10 hours a day, which will complete 40 hours. and you do not work on rest of the week. in this case, it will only calculate 40 reg hours and no OT for that week. — Binit Patel
So then why does your OT column indicate many instances of "OT"? That column is confusing me; I can't tell how you calculate it. Edit - nevermind I think I understand; those aren't really "OT" hours, those are just additional hours beyond the daily usual; the actual "OT" calculation happens only weekly. — Grade 'Eh' Bacon

Grade 'Eh' Bacon Grade 'Eh' Bacon · Accepted Answer · 2016-02-19T21:30:50

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.

how to calculate overtime in excel using by weekly pay?

3 Answers