# Formula to calculate split hours across shifts

#### cpaps_run

##### New Member
Hey all!
I'm trying to pull out data from an extract that contains employees with their start and finish times worked and split the hours they've worked across 1,2 or 3 different shift times.
My problem is the extract doesn't give me a date, just an AM/PM time for both start & finish so there are people working over midnight and midday.
Second problem being I have 3 shifts (0600-1400, 1400-2200, 2200-0600) and almost everyone will work across more than one shift time.
I have been playing around with formulas for literal hours - my focus was getting the overnight shift nailed and I thought I did last night but then looked at it again this morning and realized I hadn't factored in the fact that someone might start during that shift but their finish time will fall into the other shift.
Help me! Before I spend another 4hrs frying my brain on this!
Also - I need each shifts hours from each employee to fall into a different column ie; someone worked 4hrs across night and 3hrs across day then I'd need to see 4.0 in Column B and 3.0 in Column C etc.

The formula I am using for overnight workers calc (which is not up to scratch it's just where I've given up) is..

=IF(OR(ISNUMBER(SEARCH("*PM*",G13)),ISNUMBER(SEARCH("*NIGHT*",G13)),ISNUMBER(SEARCH("*ARVO*",G13)),ISNUMBER(SEARCH("*ARVO/NIGHT",G13))),IF(E13>4,E13-0.2,IF(AND(E13>4,E13<6),E13-0.3,IF(AND(E13>6,E13<9),E13-0.8,IF(E13>9,E13-1.1,E13)))),"0")

Again - heeeeelllppp

TIA

#### Attachments

• Excel_1.PNG
46.3 KB · Views: 11

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### AhoyNC

##### Well-known Member
Maybe one of this links will help.

#### Sulprobil

##### Board Regular
A similar problem has been solved here:

#### cpaps_run

##### New Member
Just in case anyone needs this in the future....
I set cells E2 (2200), F2 (1400), G2 (0600), E3 (2159) with my shift start times and the back end of my afternoon shift.
Column N refers to the employee clock in time, Column O refers to their clock out time.

This is the formula that I'm using...

=IFERROR(IF(AND(OR(N13<\$G\$2,N13>\$E\$3),N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),(MOD(O13-N13,1))*24,IF(AND(N13<\$E\$2,N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),24*(MOD(O13-\$E\$2,1)),IF(AND(OR(N13<\$G\$2,N13>\$E\$3),OR(O13<\$E\$2,O13>\$G\$2)),(MOD(\$G\$2-N13,1))*24,0))),"0")

Seems to be working well but a pressure test this week should be interesting.

#### Sulprobil

##### Board Regular

Just in case anyone needs this in the future....
I set cells E2 (2200), F2 (1400), G2 (0600), E3 (2159) with my shift start times and the back end of my afternoon shift.
Column N refers to the employee clock in time, Column O refers to their clock out time.

This is the formula that I'm using...

=IFERROR(IF(AND(OR(N13<\$G\$2,N13>\$E\$3),N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),(MOD(O13-N13,1))*24,IF(AND(N13<\$E\$2,N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),24*(MOD(O13-\$E\$2,1)),IF(AND(OR(N13<\$G\$2,N13>\$E\$3),OR(O13<\$E\$2,O13>\$G\$2)),(MOD(\$G\$2-N13,1))*24,0))),"0")

Seems to be working well but a pressure test this week should be interesting.
If I am not mistaken, your formula is not correct, sorry (see rows 13 and 15):
Test.xlsx
NOP
1321:5906:010
1422:0006:008
1521:5006:100
Sheet1
Cell Formulas
RangeFormula
P13:P15P13=IFERROR(IF(AND(OR(N13<\$G\$2,N13>\$E\$3),N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),(MOD(O13-N13,1))*24,IF(AND(N13<\$E\$2,N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),24*(MOD(O13-\$E\$2,1)),IF(AND(OR(N13<\$G\$2,N13>\$E\$3),OR(O13<\$E\$2,O13>\$G\$2)),(MOD(\$G\$2-N13,1))*24,0))),"0")

#### cpaps_run

##### New Member
If I am not mistaken, your formula is not correct, sorry (see rows 13 and 15):
Test.xlsx
NOP
1321:5906:010
1422:0006:008
1521:5006:100
Sheet1
Cell Formulas
RangeFormula
P13:P15P13=IFERROR(IF(AND(OR(N13<\$G\$2,N13>\$E\$3),N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),(MOD(O13-N13,1))*24,IF(AND(N13<\$E\$2,N13>\$F\$2,OR(O13>\$E\$2,O13<\$G\$2)),24*(MOD(O13-\$E\$2,1)),IF(AND(OR(N13<\$G\$2,N13>\$E\$3),OR(O13<\$E\$2,O13>\$G\$2)),(MOD(\$G\$2-N13,1))*24,0))),"0")
I did actually notice this mistake after I posted. I've changed it to this - care to take a look?

Columns Q and R now represent the start and finish times.

These are my shift boundaries

=IFERROR(IF(AND(Q17>0,R17>0),IF(AND(OR(Q17<\$J\$2,Q17>\$H\$3),OR(R17>\$H\$2,R17<\$J\$2)),(MOD(R17-Q17,1))*24,IF(AND(Q17<\$H\$2,Q17>\$I\$2,OR(R17>\$H\$2,R17<\$J\$2)),24*(MOD(R17-\$H\$2,1)),IF(AND(OR(Q17<\$J\$2,Q17>\$H\$3),OR(R17<\$H\$2,R17>\$J\$2)),(MOD(\$J\$2-Q17,1))*24,IF(AND(R17>\$J\$2,R17<\$I\$2,Q17>\$I\$2,Q17<\$H\$2),(MOD(\$J\$2-\$H\$2,1))*24,0))))),0)

#### Sulprobil

##### Board Regular
1. Again, if I am not mistaken, your formula should be symmetric for H2, I2, and J2.This is because it should not matter which shift boundary you put in where as long as you maybe arrange them ascending, wrapping around 24h. But the formula is not symmetric, I think.
2. You can search here or elsewhere for working hours, for shift bonuses, or maybe for time difference excluding weekends and holidays. For more than 10 years I have seen many people investing much time into worksheet formulas to solve this. In almost all cases they got the formula wrong or they did not implement it correctly because of its complexity.
3. My suggestion ends where it started: look at and follow the link I posted above.

Replies
3
Views
145
Replies
3
Views
64
Replies
4
Views
443
Replies
0
Views
208
Replies
0
Views
424

1,127,395
Messages
5,624,479
Members
416,029
Latest member
CSM1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back