Formula to calculate split hours across shifts

cpaps_run

New Member
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
    Excel_1.PNG
    46.3 KB · Views: 11

Some videos you may like

Excel Facts

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

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
147

cpaps_run

New Member
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 12, 2020
Messages
147

ADVERTISEMENT

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
Joined
Dec 23, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

1609369793387.png



=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
Joined
May 12, 2020
Messages
147
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top