Formula to calculate split hours across shifts

[cpaps_run]

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

 

[AhoyNC]

Maybe one of this links will help.

 

[Sulprobil]

A similar problem has been solved here:

Calculate Response time using different working hours during the week and excluding weekends / bankholidays

Hello I need a formula where it calculates Response Time considering business hours Mon - Thu 9am till 9pm and Fri 9am-5pm. In addition Bankholidays and Weekends should be excluded. Ticket Received Date is shown in Column B , Call Date is shown in Column C. Formula for Response Time for...

www.mrexcel.com

 

[cpaps_run]

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]

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
	N	O	P
13	21:59	06:01	0
14	22:00	06:00	8
15	21:50	06:10	0
Sheet1

Cell Formulas
Range	Formula
P13:P15	P13	=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]

If I am not mistaken, your formula is not correct, sorry (see rows 13 and 15):

Test.xlsx
	N	O	P
13	21:59	06:01	0
14	22:00	06:00	8
15	21:50	06:10	0
Sheet1

Cell Formulas
Range	Formula
P13:P15	P13	=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]

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.