I am trying to create a formula to calculate the number of hours worked between 12a-6a (overtime pay) and number of hours worked between 6a-12a (regular pay) so avoid making calculations in our heads and possibly making mistakes. I'm using Excel 2013 with Windows.
I've tried using various IF/AND formulas and have most recently been attempting with fractions of time but I haven't been able to put together a single formula that works for the four scenarios above. I think I'll need two different but similar formulas - one to calculate Regular Hours and one to calculate Overtime Hours. This was my most recent attempt at Regular Hours which works for rows 2 & 3 in the above table but not for rows 4 & 5:
=IF(AND(C2>=0,E2<=6/24),0,IF(AND(C2>=6/24,E2<=1),(E2-C2)*24,IF(AND(C2<1,E2>=0),24-(C2*24),IF(AND(C2<6/24,E2>=6/24),(E2-6/24)*24))))
My brain is fried, I've been working on this for so long. Any help will be much appreciated.
| A | B | C | D | E | F | G | H | I | |
| 1 | Name of Employee | Clock In Date | Clock In Time | Clock Out Date | Clock Out Time | Regular Hours | Overtime Hours | Total Hours | |
| 2 | John | 1/1/20 | 12:00 AM | 1/1/20 | 6:00 AM | 0 | 6 | 6 | |
| 3 | Jane | 1/1/20 | 6:00 AM | 1/1/20 | 2:00 PM | 8 | 0 | 8 | |
| 4 | Bob | 1/1/20 | 11:00 PM | 1/2/20 | 3:00 AM | 1 | 3 | 4 | |
| 5 | Betty | 1/1/20 | 5:00 AM | 1/1/20 | 9:00 AM | 3 | 1 | 4 |
I've tried using various IF/AND formulas and have most recently been attempting with fractions of time but I haven't been able to put together a single formula that works for the four scenarios above. I think I'll need two different but similar formulas - one to calculate Regular Hours and one to calculate Overtime Hours. This was my most recent attempt at Regular Hours which works for rows 2 & 3 in the above table but not for rows 4 & 5:
=IF(AND(C2>=0,E2<=6/24),0,IF(AND(C2>=6/24,E2<=1),(E2-C2)*24,IF(AND(C2<1,E2>=0),24-(C2*24),IF(AND(C2<6/24,E2>=6/24),(E2-6/24)*24))))
My brain is fried, I've been working on this for so long. Any help will be much appreciated.
