Hours worked split across 3 different shifts (my solution only works when Col A < Col B)

S

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
165
Office Version
  1. 2010
Platform
  1. Windows
Good afternoon,
I am making a simple task very difficult and have run out of ideas so hope someone can help.
From researching posts on the forum I have managed to get this working but with problems (highlighted green works red does not)
Could anyone please advise me how to change the formulas to account for the mistakes I am seeing below (I can see the problem when Col A > Col B but I am unable to correct it):
Test data.xlsx
ABCDEF
106:00:00
216:00:00
322:00:00
400:00:00
5
6
706:00-16:0016:00-22:0022:00-06:00
8StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
918:302:1502:15:0003:30:0002:00:0007:45:00
103:5014:3008:30:0000:00:0000:00:0010:40:00
114:0014:5408:54:0000:00:0000:00:0010:54:00
124:1516:5610:00:0000:56:0000:00:0012:41:00
135:2515:3309:33:0000:00:0000:00:0010:08:00
147:4315:1207:29:0000:00:0000:00:0007:29:00
1511:4622:5604:14:0006:00:0000:56:0011:10:00
1611:4319:3404:17:0003:34:0000:00:0007:51:00
1711:4922:2704:11:0006:00:0000:27:0010:38:00
1811:4422:1804:16:0006:00:0000:18:0010:34:00
1911:3122:1104:29:0006:00:0000:11:0010:40:00
209:4516:3006:15:0000:30:0000:00:0006:45:00
2116:050:1100:11:0005:55:0002:00:0008:06:00
2215:471:4702:00:0006:00:0002:00:0010:00:00
2316:4423:4000:00:0005:16:0001:40:0006:56:00
246:1117:5309:49:0001:53:0000:00:0011:42:00
Sheet4
Cell Formulas
RangeFormula
C9:C24C9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$2,$B9+($B9<$A9))-MAX($A9,$A$1))+MAX(0,MIN($A$2+1,$B9+($B9<$A9))-1),"")
D9:D24D9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$3,$B9+($B9<$A9))-MAX($A9,$A$2)),"")
E9:E24E9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$4,$B9+($B9<$A9))-MAX($A9,$A$3)),"")
F9:F24F9=IF(COUNT($A9:$B9)=2,$B9-$A9+($B9<$A9),"")



Any help or advice would be greatly appreciated.
Many thanks for taking the time to look
Sara
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
I haven't tested your setup in excel but it looks like you just need to use A1+1 instead of A4 on the column E formula.
 
Upvote 0
jasonb75 said:
I haven't tested your setup in excel but it looks like you just need to use A1+1 instead of A4 on the column E formula.
Click to expand...
Yes you are correct...THANK YOU! Column C is incorrect also any idea where I went wrong on that one? Thank you again :)
 
Upvote 0
This looks correct at a glance, I haven't checked the results in detail though.

Book1
ABCDEF
106:00:00
216:00:00
322:00:00
4
5
6
706:00-16:0016:00-22:0022:00-06:00
8StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
918:30:0002:15:0000:00:0003:30:0004:15:0007:45:00
1003:50:0014:30:0008:30:0000:00:0000:00:0010:40:00
1104:00:0014:54:0008:54:0000:00:0000:00:0010:54:00
1204:15:0016:56:0010:00:0000:56:0000:00:0012:41:00
1305:25:0015:33:0009:33:0000:00:0000:00:0010:08:00
1407:43:0015:12:0007:29:0000:00:0000:00:0007:29:00
1511:46:0022:56:0004:14:0006:00:0000:56:0011:10:00
1611:43:0019:34:0004:17:0003:34:0000:00:0007:51:00
1711:49:0022:27:0004:11:0006:00:0000:27:0010:38:00
1811:44:0022:18:0004:16:0006:00:0000:18:0010:34:00
1911:31:0022:11:0004:29:0006:00:0000:11:0010:40:00
2009:45:0016:30:0006:15:0000:30:0000:00:0006:45:00
2116:05:0000:11:0000:00:0005:55:0002:11:0008:06:00
2215:47:0001:47:0000:13:0006:00:0003:47:0010:00:00
2316:44:0023:40:0000:00:0005:16:0001:40:0006:56:00
2406:11:0017:53:0009:49:0001:53:0000:00:0011:42:00
Sheet1
Cell Formulas
RangeFormula
C9:C24C9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$2,$B9+($B9<$A9))-MAX($A9,$A$1)),"")
D9:D24D9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$3,$B9+($B9<$A9))-MAX($A9,$A$2)),"")
E9:E24E9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$1+1,$B9+($B9<$A9))-MAX($A9,$A$3)),"")
F9:F24F9=IF(COUNT($A9:$B9)=2,$B9-$A9+($B9<$A9),"")
 
Upvote 0
jasonb75 said:
This looks correct at a glance, I haven't checked the results in detail though.

Book1
ABCDEF
106:00:00
216:00:00
322:00:00
4
5
6
706:00-16:0016:00-22:0022:00-06:00
8StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
918:30:0002:15:0000:00:0003:30:0004:15:0007:45:00
1003:50:0014:30:0008:30:0000:00:0000:00:0010:40:00
1104:00:0014:54:0008:54:0000:00:0000:00:0010:54:00
1204:15:0016:56:0010:00:0000:56:0000:00:0012:41:00
1305:25:0015:33:0009:33:0000:00:0000:00:0010:08:00
1407:43:0015:12:0007:29:0000:00:0000:00:0007:29:00
1511:46:0022:56:0004:14:0006:00:0000:56:0011:10:00
1611:43:0019:34:0004:17:0003:34:0000:00:0007:51:00
1711:49:0022:27:0004:11:0006:00:0000:27:0010:38:00
1811:44:0022:18:0004:16:0006:00:0000:18:0010:34:00
1911:31:0022:11:0004:29:0006:00:0000:11:0010:40:00
2009:45:0016:30:0006:15:0000:30:0000:00:0006:45:00
2116:05:0000:11:0000:00:0005:55:0002:11:0008:06:00
2215:47:0001:47:0000:13:0006:00:0003:47:0010:00:00
2316:44:0023:40:0000:00:0005:16:0001:40:0006:56:00
2406:11:0017:53:0009:49:0001:53:0000:00:0011:42:00
Sheet1
Cell Formulas
RangeFormula
C9:C24C9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$2,$B9+($B9<$A9))-MAX($A9,$A$1)),"")
D9:D24D9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$3,$B9+($B9<$A9))-MAX($A9,$A$2)),"")
E9:E24E9=IF(COUNT($A9:$B9)=2,MAX(0,MIN($A$1+1,$B9+($B9<$A9))-MAX($A9,$A$3)),"")
F9:F24F9=IF(COUNT($A9:$B9)=2,$B9-$A9+($B9<$A9),"")
Click to expand...
Thank you so much for taking the time.....it does work with the exception of the shift times that start before 06:00 these are still not picked up in column E as they should fall into the 22:00-06:00 shift, everything else is perfect and I am so grateful for your help.
03:50:00​
14:30:00​
08:30:0000:00:0000:00:0010:40:00
04:00:00​
14:54:00​
08:54:0000:00:0000:00:0010:54:00
04:15:00​
16:56:00​
10:00:0000:56:0000:00:0012:41:00
05:25:00​
15:33:00​
09:33:0000:00:0000:00:0010:08:00
 
Upvote 0
This looks right for those ones, the only thing I haven't checked is for longer timeframes where start and end time are in the same shift but on subsequent days.

01.12.2022.xlsx
ABCDEF
106:0016:0022:00
216:0022:0030:00
3StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
418:30:0002:15:0000:00:0003:30:0004:15:0007:45:00
503:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
604:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
704:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
805:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
907:43:0015:12:0007:29:0000:00:0000:00:0007:29:00
1011:46:0022:56:0004:14:0006:00:0000:56:0011:10:00
1111:43:0019:34:0004:17:0003:34:0000:00:0007:51:00
1211:49:0022:27:0004:11:0006:00:0000:27:0010:38:00
1311:44:0022:18:0004:16:0006:00:0000:18:0010:34:00
1411:31:0022:11:0004:29:0006:00:0000:11:0010:40:00
1509:45:0016:30:0006:15:0000:30:0000:00:0006:45:00
1616:05:0000:11:0000:00:0005:55:0002:11:0008:06:00
1715:47:0001:47:0000:13:0006:00:0003:47:0010:00:00
1816:44:0023:40:0000:00:0005:16:0001:40:0006:56:00
1917:53:0006:11:0000:00:0004:07:0008:00:0012:18:00
2003:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
2104:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
2204:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
2305:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
Sheet3
Cell Formulas
RangeFormula
C4:D23C4=IF(COUNT($A4:$B4)=2,MAX(0,MIN(C$2,$B4+($B4<$A4))-MAX($A4,C$1)),"")
E4:E23E4=IF(COUNT($A4:$B4)=2,IF($A4<0.25,MAX(0,MIN(E$2,$B4+1)-($A4+1)),MAX(0,MIN(E$2,$B4+($B4<$A4))-MAX($A4,E$1))),"")
F4:F23F4=IF(COUNT($A4:$B4)=2,$B4-$A4+($B4<$A4),"")
 
Upvote 0
jasonb75 said:
This looks right for those ones, the only thing I haven't checked is for longer timeframes where start and end time are in the same shift but on subsequent days.

01.12.2022.xlsx
ABCDEF
106:0016:0022:00
216:0022:0030:00
3StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
418:30:0002:15:0000:00:0003:30:0004:15:0007:45:00
503:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
604:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
704:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
805:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
907:43:0015:12:0007:29:0000:00:0000:00:0007:29:00
1011:46:0022:56:0004:14:0006:00:0000:56:0011:10:00
1111:43:0019:34:0004:17:0003:34:0000:00:0007:51:00
1211:49:0022:27:0004:11:0006:00:0000:27:0010:38:00
1311:44:0022:18:0004:16:0006:00:0000:18:0010:34:00
1411:31:0022:11:0004:29:0006:00:0000:11:0010:40:00
1509:45:0016:30:0006:15:0000:30:0000:00:0006:45:00
1616:05:0000:11:0000:00:0005:55:0002:11:0008:06:00
1715:47:0001:47:0000:13:0006:00:0003:47:0010:00:00
1816:44:0023:40:0000:00:0005:16:0001:40:0006:56:00
1917:53:0006:11:0000:00:0004:07:0008:00:0012:18:00
2003:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
2104:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
2204:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
2305:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
Sheet3
Cell Formulas
RangeFormula
C4:D23C4=IF(COUNT($A4:$B4)=2,MAX(0,MIN(C$2,$B4+($B4<$A4))-MAX($A4,C$1)),"")
E4:E23E4=IF(COUNT($A4:$B4)=2,IF($A4<0.25,MAX(0,MIN(E$2,$B4+1)-($A4+1)),MAX(0,MIN(E$2,$B4+($B4<$A4))-MAX($A4,E$1))),"")
F4:F23F4=IF(COUNT($A4:$B4)=2,$B4-$A4+($B4<$A4),"")
Click to expand...

Thank you - you are spot on it works perfectly! :)
I am trying cover every eventuality and have unfortunately managed to break it with the following times:
06:00:0016:00:0022:00:00
16:00:0022:00:0006:00:00
StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
21:55:0012:00:0000:00:0000:05:0008:00:0014:05:00
22:00:0006:30:0000:00:0000:00:0008:00:0008:30:00
20:00:0006:30:0000:00:0002:00:0008:00:0010:30:00


As you can see it is not registering times in column 06:00 - 16:00

In addition to this there is a further problem which could occur as a worker could legally pick up hours in shift 1 twice (as you mentioned, to do so across the other shifts would be exceeding legal working hours so I am not too concerned about this, however if there is a solution it would be good to include):
06:00:0016:00:0022:00:00
16:00:0022:00:0006:00:00
StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
15:55:0006:05:0000:05:0006:00:0008:00:0014:10:00



Your help with this is most appreciated
 
Upvote 0
I've been over it again and think I have it right for the examples. Including the second example in your previous post (with the times exceeding legal working hours) there are 12 possible options for start and end that need to be allowed for in the formulas. I'll have another look at it and test all of those possibilities to make sure that the formulas are working correctly with them but I am unlikely to be able to do that before the weekend

edit:- had a quick look and all are ok apart from the non legal ones.

01.12.2022.xlsx
ABCDEF
106:0016:0022:00
216:0022:0030:00
3StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
418:30:0002:15:0000:00:0003:30:0004:15:0007:45:00
503:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
604:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
704:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
805:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
907:43:0015:12:0007:29:0000:00:0000:00:0007:29:00
1011:46:0022:56:0004:14:0006:00:0000:56:0011:10:00
1111:43:0019:34:0004:17:0003:34:0000:00:0007:51:00
1211:49:0022:27:0004:11:0006:00:0000:27:0010:38:00
1311:44:0022:18:0004:16:0006:00:0000:18:0010:34:00
1411:31:0022:11:0004:29:0006:00:0000:11:0010:40:00
1509:45:0016:30:0006:15:0000:30:0000:00:0006:45:00
1616:05:0000:11:0000:00:0005:55:0002:11:0008:06:00
1715:47:0001:47:0000:13:0006:00:0003:47:0010:00:00
1816:44:0023:40:0000:00:0005:16:0001:40:0006:56:00
1917:53:0006:11:0000:11:0004:07:0008:00:0012:18:00
2003:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
2104:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
2204:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
2305:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
2421:55:0012:00:0006:00:0000:05:0008:00:0014:05:00
2522:00:0006:30:0000:30:0000:00:0008:00:0008:30:00
2620:00:0006:30:0000:30:0002:00:0008:00:0010:30:00
2715:55:0006:05:0000:10:0006:00:0008:00:0014:10:00
Sheet3
Cell Formulas
RangeFormula
C4:C27C4=IF(COUNT($A4:$B4)=2,MAX(0,F4-(D4+E4)),"")
D4:D27D4=IF(COUNT($A4:$B4)=2,MAX(0,MIN(D$2,$B4+($B4<$A4))-MAX($A4,D$1)),"")
E4:E27E4=IF(COUNT($A4:$B4)=2,IF($A4<0.25,MAX(0,MIN(E$2,$B4+1)-($A4+1)),MAX(0,MIN(E$2,$B4+($B4<$A4))-MAX($A4,E$1))),"")
F4:F27F4=IF(COUNT($A4:$B4)=2,MOD($B4-$A4,1),"")
 
Last edited:
Upvote 0
Solution
jasonb75 said:
I've been over it again and think I have it right for the examples. Including the second example in your previous post (with the times exceeding legal working hours) there are 12 possible options for start and end that need to be allowed for in the formulas. I'll have another look at it and test all of those possibilities to make sure that the formulas are working correctly with them but I am unlikely to be able to do that before the weekend

edit:- had a quick look and all are ok apart from the non legal ones.

01.12.2022.xlsx
ABCDEF
106:0016:0022:00
216:0022:0030:00
3StartEndShift 1 10hrsShift 2 6hrsShift 3 8hrsTOTAL
418:30:0002:15:0000:00:0003:30:0004:15:0007:45:00
503:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
604:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
704:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
805:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
907:43:0015:12:0007:29:0000:00:0000:00:0007:29:00
1011:46:0022:56:0004:14:0006:00:0000:56:0011:10:00
1111:43:0019:34:0004:17:0003:34:0000:00:0007:51:00
1211:49:0022:27:0004:11:0006:00:0000:27:0010:38:00
1311:44:0022:18:0004:16:0006:00:0000:18:0010:34:00
1411:31:0022:11:0004:29:0006:00:0000:11:0010:40:00
1509:45:0016:30:0006:15:0000:30:0000:00:0006:45:00
1616:05:0000:11:0000:00:0005:55:0002:11:0008:06:00
1715:47:0001:47:0000:13:0006:00:0003:47:0010:00:00
1816:44:0023:40:0000:00:0005:16:0001:40:0006:56:00
1917:53:0006:11:0000:11:0004:07:0008:00:0012:18:00
2003:50:0014:30:0008:30:0000:00:0002:10:0010:40:00
2104:00:0014:54:0008:54:0000:00:0002:00:0010:54:00
2204:15:0016:56:0010:00:0000:56:0001:45:0012:41:00
2305:25:0015:33:0009:33:0000:00:0000:35:0010:08:00
2421:55:0012:00:0006:00:0000:05:0008:00:0014:05:00
2522:00:0006:30:0000:30:0000:00:0008:00:0008:30:00
2620:00:0006:30:0000:30:0002:00:0008:00:0010:30:00
2715:55:0006:05:0000:10:0006:00:0008:00:0014:10:00
Sheet3
Cell Formulas
RangeFormula
C4:C27C4=IF(COUNT($A4:$B4)=2,MAX(0,F4-(D4+E4)),"")
D4:D27D4=IF(COUNT($A4:$B4)=2,MAX(0,MIN(D$2,$B4+($B4<$A4))-MAX($A4,D$1)),"")
E4:E27E4=IF(COUNT($A4:$B4)=2,IF($A4<0.25,MAX(0,MIN(E$2,$B4+1)-($A4+1)),MAX(0,MIN(E$2,$B4+($B4<$A4))-MAX($A4,E$1))),"")
F4:F27F4=IF(COUNT($A4:$B4)=2,MOD($B4-$A4,1),"")
Click to expand...
This is fantastic!!!! I can't thank you enough :) I am just going through it all now but it looks great, your help has been amazing this would have taken me years to figure out!
I think with regard to the none legal ones I should be ok - the sheet is to be used for HGV drivers so the legal implications of exceeding hours is more serious than them not getting paid for it!!!
THANK YOU
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
INDEX MATCH
Replies
4
Views
372
kweaver
kweaver
P
  • Solved
Calculate time difference between two times with removing non working hours
Replies
6
Views
2K
pratik554
P
F
  • Question Question
cut defined cells from a sheet paste them on another sheet then delete incompletes rows
Replies
2
Views
725
Flavien
F
M
  • Question Question
Excel Formula help
Replies
4
Views
865
Sam_D_Ben
S
K
  • Solved
Help with Macro, as it is not extracting all the data.
Replies
7
Views
963
Fluff
Fluff

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

Share this page

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
Back
Top