How to check if a 2 specific time falls in between 2 times (Shifts) in Excel?

athishvikram

New Member
Joined
Jul 26, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
1.I want to find out if an event with start time and end time falls "inside" or "Outside" working shifts for associates.

2.Segregate and calculate the production duration that falls within shift hours or outside shift hours.

(The Shift timings are 5pm to 2am which would be the next day so that is something to look out of for.)

In the attached image you can see that Row 2 : Stamp_IST is the starting time and Stamps_IST End is the end of that event (Production Hrs),

For example in row 6 the user starts his production inside shift 1:24:37 AM but ends his production outside shift 3:36:02 AM so ideally he has worked only for around 6 mins within his shift and only duration of 6 mins to be taken in account where as the rest of the duration should fall outside shift. so is there a way formula or a macro which would segregate give me the duration spent within and outside shift hrs.
 

Attachments

  • Timings doubt 3.png
    Timings doubt 3.png
    104.4 KB · Views: 84

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
A similar question has been discussed here:
 
Upvote 0
A similar question has been discussed here:
Thank you so much for letting me know.. I will look into that thread you had shared,,
 
Upvote 0
Consider:

Book1
ABCDEFGHI
1Duration Bracket
2DateShift Start TimeShift End TimeStmp_ISTStmp_IST EndProduction (Hrs)Shift BracketInside ShiftOutside Shift (Hrs)
37/13/20205:00:00 PM2:00:00 AM5:10:29 PM5:29:50 PM0.32Within0:19:210:00:00
47/13/20205:00:00 PM2:00:00 AM5:34:04 PM5:45:37 PM0.19Within0:11:330:00:00
57/13/20205:00:00 PM2:00:00 AM5:57:54 PM6:00:36 PM0.05Within0:02:420:00:00
67/13/20205:00:00 PM2:00:00 AM1:24:37 AM3:36:02 AM2.19Within0:35:231:36:02
Sheet11
Cell Formulas
RangeFormula
H3:H6H3=MAX(0,MIN(C3+(C3<B3),E3+(E3<B3))-MAX(B3,D3+(D3<B3)))
I3:I6I3=E3-D3+(E3<D3)-H3
F3:F6F3=(E3-D3+(E3<D3))*24


There are a few issues with this. First, on your row 6, your example shows 6 minutes Inside Shift. Wouldn't it be 2:00:00 AM - 1:24:37 AM, or 35:23 minutes? Next, should the C6 value be 2:00:00 AM, not 1:55:00 AM? That doesn't matter, the formulas look at the value in the cell, the 2:00:00 AM value is not hardcoded in the formula, but it does affect your sample result.

Also, I jumped through some hoops to handle the cases where the dates span 2 days. It would make things easier if the value in the B:E columns had dates attached to the time. I'm not entirely sure that the additions I made to the formulas will work in all cases. Test it thoroughly.
 
Upvote 0
Consider:

Book1
ABCDEFGHI
1Duration Bracket
2DateShift Start TimeShift End TimeStmp_ISTStmp_IST EndProduction (Hrs)Shift BracketInside ShiftOutside Shift (Hrs)
37/13/20205:00:00 PM2:00:00 AM5:10:29 PM5:29:50 PM0.32Within0:19:210:00:00
47/13/20205:00:00 PM2:00:00 AM5:34:04 PM5:45:37 PM0.19Within0:11:330:00:00
57/13/20205:00:00 PM2:00:00 AM5:57:54 PM6:00:36 PM0.05Within0:02:420:00:00
67/13/20205:00:00 PM2:00:00 AM1:24:37 AM3:36:02 AM2.19Within0:35:231:36:02
Sheet11
Cell Formulas
RangeFormula
H3:H6H3=MAX(0,MIN(C3+(C3<B3),E3+(E3<B3))-MAX(B3,D3+(D3<B3)))
I3:I6I3=E3-D3+(E3<D3)-H3
F3:F6F3=(E3-D3+(E3<D3))*24


There are a few issues with this. First, on your row 6, your example shows 6 minutes Inside Shift. Wouldn't it be 2:00:00 AM - 1:24:37 AM, or 35:23 minutes? Next, should the C6 value be 2:00:00 AM, not 1:55:00 AM? That doesn't matter, the formulas look at the value in the cell, the 2:00:00 AM value is not hardcoded in the formula, but it does affect your sample result.

Also, I jumped through some hoops to handle the cases where the dates span 2 days. It would make things easier if the value in the B:E columns had dates attached to the time. I'm not entirely sure that the additions I made to the formulas will work in all cases. Test it thoroughly.

Hi Eric,

Thank you so much for the reply.

Wouldn't it be 2:00:00 AM - 1:24:37 AM, or 35:23 minutes? - Yes, my bad i made a mistake you are right here.
should the C6 value be 2:00:00 AM, not 1:55:00 AM? - Yes you are right again.
It would make things easier if the value in the B:E columns had dates attached to the time. - I can get that for you

Your method worked perfectly for most of the cases but there seems to be cases where the formula is stumped. Please find the file attached image where the division of duration was incorrect.

Conditions to satisfyDescriptionStatus
Condition 1Start time stamp (F column) & End time stamp (G column) falls inside shiftCondition Statisfied
Condition 2Start time stamp (F column) & End time stamp (G column) falls outside shift (if both fall outside shift)Condition Statisfied
Condition 3Start time stamp (F column) falls inside shift & End time stamp (G column) falls outside shiftCondition Statisfied
Condition 4Start time stamp (F column) outside shift & End time stamp (G column) falls inside shift timeCondition Not Statisfied

Formulas

RangeCellsFormula
I13:I21I13=IF(MOD(1+F13-D13,1)=MIN(MOD(1+F13-D13,1), MOD(1+F13-E13,1)), "Within Shift", "Outside Shift")
J13:J20J13=MAX(0,MIN(E13+(E13<D13),G13+(G13<D13))-MAX(D13,F13+(F13<D13)))
K13:K20K13=G13-F13+(G13<F13)-J13
L13:L20L13=HOUR(J13)*60+MINUTE(J13)
M13:M20M13=HOUR(K13)*60+MINUTE(K13)
 

Attachments

  • Timings doubt 4.png
    Timings doubt 4.png
    243 KB · Views: 24
Upvote 0
Hi Eric,

I made a mistake in the image (Column N & O). I interchanged the numbers. The attached image now is correct.

Timings doubt 5.jpg
 
Upvote 0
Kudos on your testing. Nicely done coming up with specific test cases.

Next, in the future consider using the XL2BB add-in. Look for the link in my signature, or in the reply box. It's very easy to use, and generates a grid like I did in post 4. A huge advantage of it is that if someone reading the forum wants to work on a problem they just need to click on the page symbol

1595954738208.png


in the grid, then go to Excel and click Paste, and the entire grid is pasted in a worksheet. If you just paste a picture, that requires the helpers to manually enter everything, and most people won't bother with a large sheet.

Finally, regarding your Condition 4. This goes back to trying to decide what day the time goes with. Since you said you could add the dates to the B:E columns, here's how that would look:

Book1
ABCDEFGHIJK
1Duration Bracket
2DateShift Start TimeShift End TimeStmp_ISTStmp_IST EndProduction (Hrs)Shift BracketInside ShiftOutside Shift (Hrs)
37/13/20207/13/2020 17:007/14/2020 2:007/13/2020 17:107/13/2020 17:290.320:19:210:00:00Condition 1
47/13/20207/13/2020 17:007/14/2020 2:007/14/2020 17:347/14/2020 17:450.190:00:000:11:33Condition 2
57/13/20207/13/2020 17:007/14/2020 2:007/13/2020 23:577/14/2020 3:003.052:02:061:00:36Condition 3
67/13/20207/13/2020 17:007/14/2020 2:007/14/2020 1:247/14/2020 3:362.190:35:231:36:02Condition 3
77/18/20207/18/2020 17:007/19/2020 2:007/18/2020 16:027/18/2020 17:301.460:30:080:57:16Condition 4
Sheet12
Cell Formulas
RangeFormula
H3:H7H3=MAX(0,MIN(C3,E3)-MAX(B3,D3))
I3:I7I3=E3-D3-H3
F3:F7F3=(E3-D3)*24


The formulas are actually shorter. If you have an issue putting the date in with the times, let me know. I can try to adapt the existing formula, but it could be quite tricky, since we've seen that 17:00 PM could be before the shift starts, or after it ends.
 
Upvote 0
Hi Eric,

Again really appreciate what you are doing, without your help I would have been helpless.

Now we are facing yet another issue.

Issue : Time with dates.

As you requested I was able to pull data with time and date but there is a slight hiccup. So as per my company's back end databases the date and time is considered a little differently.

So during ideal cases let's say 5:00 pm 2:00 am (IST) shift ,
Example 1 : The Event Start Time Stamp : 7/29/2020 12:24 am and Shift End Time stamp : 7/30/2020 2.00 am .
But in our databases the same time is considered as Event Start Time Stamp : 7/29/2020 12:24 am - Shift End Time stamp : 7/29/2020 2.00 am, the difference here is, the date and time is captured as per the shift timings. So the even if the times crosses 12:00 am, the date is still considered as 7/29/2020 and not as 7/30/2020.
This again is causing a mismatch in numbers if we considered data & time into the formula. I tried a lot to get it sorted but again I am at loss.

The ones that are marked in yellow is what i am talking about.

Sorry about the use of so many formulas I am still an novice is in excel and the dump is huge so I convert the range into a table, to simplify my work.

Template v.3.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1start_datetimeend_datetimedurationevent_datetimetimestmpDateStmp_ISTL1Start date ISTStart Time ISTEnd date ISTEnd Time IST 1End Time IST 1 (2)Start_datetime ISTEnd_datetime ISTCurrent ShiftCurrent ScheduleShift Start TimeShift End TimeShift End DateShift Start_datetimeShift End_datetimeProd SegregationInside Shift (Hrs)Outside Shift (Hrs)
27/13/2020 20:257/13/2020 20:2666.2937/13/2020 20:268:25:32 PM7/13/20201:55:32 AMProduction7/13/20201:55 AM7/13/20201:56:39 AM1:56:39 AM7/13/2020 1:557/13/2020 1:5617:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
37/13/2020 20:017/13/2020 20:0119.7097/13/2020 20:268:01:22 PM7/13/20201:31:22 AMProduction7/13/20201:31 AM7/13/20201:31:42 AM1:31:42 AM7/13/2020 1:317/13/2020 1:3117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
47/13/2020 19:417/13/2020 19:4121.2527/13/2020 20:267:41:20 PM7/13/20201:11:20 AMProduction7/13/20201:11 AM7/13/20201:11:41 AM1:11:41 AM7/13/2020 1:117/13/2020 1:1117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
57/13/2020 19:407/13/2020 19:4152.4987/13/2020 20:267:40:27 PM7/13/20201:10:27 AMProduction7/13/20201:10 AM7/13/20201:11:20 AM1:11:20 AM7/13/2020 1:107/13/2020 1:1117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
67/13/2020 18:247/13/2020 19:404537.8347/13/2020 20:266:24:48 PM7/13/202011:54:48 PMProduction7/13/202011:54 PM7/13/20201:10:26 AM1:10:26 AM7/13/2020 23:547/13/2020 1:1017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.00-0.95
77/13/2020 17:437/13/2020 17:57850.4777/13/2020 20:265:43:23 PM7/13/202011:13:23 PMProduction7/13/202011:13 PM7/13/202011:27:34 PM11:27:34 PM7/13/2020 23:137/13/2020 23:2717:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.010.00
87/13/2020 16:597/13/2020 17:01132.8787/13/2020 20:264:59:00 PM7/13/202010:29:00 PMProduction7/13/202010:29 PM7/13/202010:31:13 PM10:31:13 PM7/13/2020 22:297/13/2020 22:3117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
97/13/2020 16:547/13/2020 16:58254.8857/13/2020 20:264:54:05 PM7/13/202010:24:05 PMProduction7/13/202010:24 PM7/13/202010:28:20 PM10:28:20 PM7/13/2020 22:247/13/2020 22:2817:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
107/13/2020 16:467/13/2020 16:52357.3917/13/2020 20:264:46:20 PM7/13/202010:16:20 PMProduction7/13/202010:16 PM7/13/202010:22:17 PM10:22:17 PM7/13/2020 22:167/13/2020 22:2217:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
117/13/2020 11:457/13/2020 16:0515568.9067/13/2020 20:2611:45:53 AM7/13/20205:15:53 PMProduction7/13/20205:15 PM7/13/20209:35:22 PM9:35:22 PM7/13/2020 17:157/13/2020 21:3517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.180.00
127/14/2020 20:207/14/2020 20:30636.6157/15/2020 11:568:20:14 PM7/14/20201:50:14 AMProduction7/14/20201:50 AM7/14/20202:00:50 AM2:00:50 AM7/14/2020 1:507/14/2020 2:0017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.01
137/14/2020 20:157/14/2020 20:20247.7897/15/2020 11:568:15:53 PM7/14/20201:45:53 AMProduction7/14/20201:45 AM7/14/20201:50:00 AM1:50:00 AM7/14/2020 1:457/14/2020 1:5017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.00
147/14/2020 19:357/14/2020 19:50894.197/15/2020 11:567:35:46 PM7/14/20201:05:46 AMProduction7/14/20201:05 AM7/14/20201:20:40 AM1:20:40 AM7/14/2020 1:057/14/2020 1:2017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.01
157/14/2020 19:357/14/2020 19:3524.5347/15/2020 11:567:35:21 PM7/14/20201:05:21 AMProduction7/14/20201:05 AM7/14/20201:05:46 AM1:05:46 AM7/14/2020 1:057/14/2020 1:0517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.00
167/14/2020 17:467/14/2020 19:356500.5277/15/2020 11:565:46:50 PM7/14/202011:16:50 PMProduction7/14/202011:16 PM7/14/20201:05:10 AM1:05:10 AM7/14/2020 23:167/14/2020 1:0517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.00-0.92
177/14/2020 17:297/14/2020 17:43792.7647/15/2020 11:565:29:47 PM7/14/202010:59:47 PMProduction7/14/202010:59 PM7/14/202011:13:00 PM11:13:00 PM7/14/2020 22:597/14/2020 23:1317:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.010.00
187/14/2020 17:087/14/2020 17:291235.4157/15/2020 11:565:08:37 PM7/14/202010:38:37 PMProduction7/14/202010:38 PM7/14/202010:59:12 PM10:59:12 PM7/14/2020 22:387/14/2020 22:5917:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.010.00
197/13/2020 18:027/13/2020 19:355573.0147/13/2020 20:306:02:16 PM7/13/202011:32:16 PMProduction7/13/202011:32 PM7/13/20201:05:09 AM1:05:09 AM7/13/2020 23:327/13/2020 1:0517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.00-0.94
207/13/2020 17:397/13/2020 17:42191.4647/13/2020 20:305:39:21 PM7/13/202011:09:21 PMProduction7/13/202011:09 PM7/13/202011:12:32 PM11:12:32 PM7/13/2020 23:097/13/2020 23:1217:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
217/13/2020 16:267/13/2020 17:002066.3797/13/2020 20:304:26:24 PM7/13/20209:56:24 PMProduction7/13/20209:56 PM7/13/202010:30:51 PM10:30:51 PM7/13/2020 21:567/13/2020 22:3017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.020.00
Sheet6
Cell Formulas
RangeFormula
I2:I21I2=INT([@[start_datetime]])
J2:J21J2=([@[Stmp_IST]]-INT([Stmp_IST]))
K2:K21K2=INT([@[end_datetime]])
L2:L21L2=([@[end_datetime]]-INT([@[end_datetime]]))+TIME(5,30,0)
M2:M21M2=[@[End Time IST 1]]-INT([@[End Time IST 1]])
N2:N21N2=INT([@[Start date IST]])+[@[Start Time IST]]
O2:O21O2=INT([@[End date IST]])+[@[End Time IST 1 (2)]]
R2:R21R2=IF([@[Current Shift]]="07:00 - 16:00",TIME(7,0,0),IF([@[Current Shift]]="17:00 - 02:00",TIME(17,0,0),IF([@[Current Shift]]="10:00 - 19:00",TIME(10,0,0),"")))
S2:S21S2=IF([@[Shift Start Time]]=TIME(7,0,0),TIME(16,0,0),IF([@[Shift Start Time]]=TIME(17,0,0),TIME(2,0,0),IF([@[Shift Start Time]]=TIME(10,0,0),TIME(19,0,0),"")))
T2:T21T2=IF([@[Shift End Time]]=TIME(2,0,0),[@Date]+1,[@Date])
U2:U21U2=INT([@Date])+[@[Shift Start Time]]
V2:V21V2=INT([@[Shift End Date]])+[@[Shift End Time]]
W2:W21W2=IF([@L1]="Production","Production","")
X2:X21X2=IF([@[Prod Segregation]]="","",MAX(0,MIN([@[Shift End_datetime]],[@[End_datetime IST]])-MAX([@[Shift Start_datetime]],[@[Start_datetime IST]])))
Y2:Y21Y2=[@[End_datetime IST]]-[@[Start_datetime IST]]-[@[Inside Shift (Hrs)]]


As you said if this is an issues that we cannot solve then we would have to go back to just using the time as a primary value
 
Upvote 0
Hi Eric,

I just realized that if you copy the excel (XL2BB) the format of the formulas are copying differently. So make it east for you i am copying the table again

Note:

01.The Column headers in blue is what i get as an export from our database. The Column headers in red has formulas.

Formula Reference:

01. Start date IST I (Row) =INT(A2) - To separate event start date " from "start_datetime"
02. Start Time IST J (Row) =G2-INT(G2) -To separate event start time from "Stmp_IST" - Here the time is already converted to IST in row G but the date 1/1/1900 is in the cell.
03. End date IST K (Row) =INT(B2) - To seperate event end date from "end_datetime"
04. End Time IST 1 L (Row) =B2-INT(B2)+TIME(5,30,0) - To separate event end time from "end_datetime" and convert to IST
05. End Time IST 1 (2) M (Row) =L2-INT(L2) - To seperate "1/1/1900" from "End date IST"
06. Start_datetime IST N (Row) =INT(I2)+J2 - To concatenate "Start date IST" and "Start Time IST"
07. End_datetime IST O (Row) =INT(K2)+M2 - To concatenate "End date IST" and "End Time IST 1 (2)"
08. Shift Start Time R (Row) =IF(P2="07:00 - 16:00",TIME(7,0,0),IF(P2="17:00 - 02:00",TIME(17,0,0),IF(P2="10:00 - 19:00",TIME(10,0,0),""))) - To get the correct time format to concatenate in U (Row).
09. Shift End Time S (Row) =IF(R2=TIME(7,0,0),TIME(16,0,0),IF(R2=TIME(17,0,0),TIME(2,0,0),IF(R2=TIME(10,0,0),TIME(19,0,0),""))) - To get the correct time format to concatenate in V (Row).
10. Shift End Date T (Row) =IF(S2=TIME(2,0,0),F2+1,F2) - To get end date , adding (+1) so the shift end date would be the next day to accomodate the formula in X (Row) , i.e, if end date is 7/13/2020 1:30 am according to my database but to support the formula X (Row) i am adding (+1) so it would be 7/14/2020 1:30 am.
11. Shift Start_datetime U (Row) =INT(F2)+R2 - To concatenate "Date" "F (Row)" and "Shift Start Time".
12. Shift End_datetime V (Row) =INT(T2)+S2 - To concatenate "Shift End Time" and "Shift End Date".
13. Prod Segregation W (Row) =IF(H2="Production","Production","") - To segregarte Production bracket amongst other brackets.

Template v.3.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1start_datetimeend_datetimedurationevent_datetimetimestmpDateStmp_ISTL1Start date ISTStart Time ISTEnd date ISTEnd Time IST 1End Time IST 1 (2)Start_datetime ISTEnd_datetime ISTCurrent ShiftCurrent ScheduleShift Start TimeShift End TimeShift End DateShift Start_datetimeShift End_datetimeProd SegregationInside Shift (Hrs)Outside Shift (Hrs)
27/13/2020 20:257/13/2020 20:2666.2937/13/2020 20:268:25:32 PM7/13/20201:55:32 AMProduction7/13/20201:55 AM7/13/20201:56:39 AM1:56:39 AM7/13/2020 1:557/13/2020 1:5617:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
37/13/2020 20:017/13/2020 20:0119.7097/13/2020 20:268:01:22 PM7/13/20201:31:22 AMProduction7/13/20201:31 AM7/13/20201:31:42 AM1:31:42 AM7/13/2020 1:317/13/2020 1:3117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
47/13/2020 19:417/13/2020 19:4121.2527/13/2020 20:267:41:20 PM7/13/20201:11:20 AMProduction7/13/20201:11 AM7/13/20201:11:41 AM1:11:41 AM7/13/2020 1:117/13/2020 1:1117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
57/13/2020 19:407/13/2020 19:4152.4987/13/2020 20:267:40:27 PM7/13/20201:10:27 AMProduction7/13/20201:10 AM7/13/20201:11:20 AM1:11:20 AM7/13/2020 1:107/13/2020 1:1117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
67/13/2020 18:247/13/2020 19:404537.8347/13/2020 20:266:24:48 PM7/13/202011:54:48 PMProduction7/13/202011:54 PM7/13/20201:10:26 AM1:10:26 AM7/13/2020 23:547/13/2020 1:1017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.00-0.95
77/13/2020 17:437/13/2020 17:57850.4777/13/2020 20:265:43:23 PM7/13/202011:13:23 PMProduction7/13/202011:13 PM7/13/202011:27:34 PM11:27:34 PM7/13/2020 23:137/13/2020 23:2717:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.010.00
87/13/2020 16:597/13/2020 17:01132.8787/13/2020 20:264:59:00 PM7/13/202010:29:00 PMProduction7/13/202010:29 PM7/13/202010:31:13 PM10:31:13 PM7/13/2020 22:297/13/2020 22:3117:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
97/13/2020 16:547/13/2020 16:58254.8857/13/2020 20:264:54:05 PM7/13/202010:24:05 PMProduction7/13/202010:24 PM7/13/202010:28:20 PM10:28:20 PM7/13/2020 22:247/13/2020 22:2817:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
107/13/2020 16:467/13/2020 16:52357.3917/13/2020 20:264:46:20 PM7/13/202010:16:20 PMProduction7/13/202010:16 PM7/13/202010:22:17 PM10:22:17 PM7/13/2020 22:167/13/2020 22:2217:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
117/13/2020 11:457/13/2020 16:0515568.9067/13/2020 20:2611:45:53 AM7/13/20205:15:53 PMProduction7/13/20205:15 PM7/13/20209:35:22 PM9:35:22 PM7/13/2020 17:157/13/2020 21:3517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.180.00
127/14/2020 20:207/14/2020 20:30636.6157/15/2020 11:568:20:14 PM7/14/20201:50:14 AMProduction7/14/20201:50 AM7/14/20202:00:50 AM2:00:50 AM7/14/2020 1:507/14/2020 2:0017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.01
137/14/2020 20:157/14/2020 20:20247.7897/15/2020 11:568:15:53 PM7/14/20201:45:53 AMProduction7/14/20201:45 AM7/14/20201:50:00 AM1:50:00 AM7/14/2020 1:457/14/2020 1:5017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.00
147/14/2020 19:357/14/2020 19:50894.197/15/2020 11:567:35:46 PM7/14/20201:05:46 AMProduction7/14/20201:05 AM7/14/20201:20:40 AM1:20:40 AM7/14/2020 1:057/14/2020 1:2017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.01
157/14/2020 19:357/14/2020 19:3524.5347/15/2020 11:567:35:21 PM7/14/20201:05:21 AMProduction7/14/20201:05 AM7/14/20201:05:46 AM1:05:46 AM7/14/2020 1:057/14/2020 1:0517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.000.00
167/14/2020 17:467/14/2020 19:356500.5277/15/2020 11:565:46:50 PM7/14/202011:16:50 PMProduction7/14/202011:16 PM7/14/20201:05:10 AM1:05:10 AM7/14/2020 23:167/14/2020 1:0517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.00-0.92
177/14/2020 17:297/14/2020 17:43792.7647/15/2020 11:565:29:47 PM7/14/202010:59:47 PMProduction7/14/202010:59 PM7/14/202011:13:00 PM11:13:00 PM7/14/2020 22:597/14/2020 23:1317:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.010.00
187/14/2020 17:087/14/2020 17:291235.4157/15/2020 11:565:08:37 PM7/14/202010:38:37 PMProduction7/14/202010:38 PM7/14/202010:59:12 PM10:59:12 PM7/14/2020 22:387/14/2020 22:5917:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/15/20207/14/2020 17:007/15/2020 2:00Production0.010.00
197/13/2020 18:027/13/2020 19:355573.0147/13/2020 20:306:02:16 PM7/13/202011:32:16 PMProduction7/13/202011:32 PM7/13/20201:05:09 AM1:05:09 AM7/13/2020 23:327/13/2020 1:0517:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.00-0.94
207/13/2020 17:397/13/2020 17:42191.4647/13/2020 20:305:39:21 PM7/13/202011:09:21 PMProduction7/13/202011:09 PM7/13/202011:12:32 PM11:12:32 PM7/13/2020 23:097/13/2020 23:1217:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.000.00
217/13/2020 16:267/13/2020 17:002066.3797/13/2020 20:304:26:24 PM7/13/20209:56:24 PMProduction7/13/20209:56 PM7/13/202010:30:51 PM10:30:51 PM7/13/2020 21:567/13/2020 22:3017:00 - 02:00Mon-Fri5:00:00 PM2:00:00 AM7/14/20207/13/2020 17:007/14/2020 2:00Production0.020.00
Sheet7
Cell Formulas
RangeFormula
I2:I21I2=INT(A2)
J2:J21J2=G2-INT(G2)
K2:K21K2=INT(B2)
L2:L21L2=B2-INT(B2)+TIME(5,30,0)
M2:M21M2=L2-INT(L2)
N2:N21N2=INT(I2)+J2
O2:O21O2=INT(K2)+M2
R2:R21R2=IF(P2="07:00 - 16:00",TIME(7,0,0),IF(P2="17:00 - 02:00",TIME(17,0,0),IF(P2="10:00 - 19:00",TIME(10,0,0),"")))
S2:S21S2=IF(R2=TIME(7,0,0),TIME(16,0,0),IF(R2=TIME(17,0,0),TIME(2,0,0),IF(R2=TIME(10,0,0),TIME(19,0,0),"")))
T2:T21T2=IF(S2=TIME(2,0,0),F2+1,F2)
U2:U21U2=INT(F2)+R2
V2:V21V2=INT(T2)+S2
W2:W21W2=IF(H2="Production","Production","")
X2:X21X2=IF(W2="","",MAX(0,MIN(V2,O2)-MAX(U2,N2)))
Y2:Y21Y2=O2-N2-X2
 
Upvote 0
MrExcel_Time_Period_in_Shift.xlsm
ABCD
1start_datetimeend_datetimeInsideOutside
213.07.2020 01:5513.07.2020 01:560:000:01
313.07.2020 01:3113.07.2020 01:310:000:00
413.07.2020 01:1113.07.2020 01:110:000:00
513.07.2020 01:1013.07.2020 01:110:000:01
613.07.2020 23:5413.07.2020 01:100:00-22:43
713.07.2020 23:1313.07.2020 23:270:140:00
813.07.2020 22:2913.07.2020 22:310:020:00
913.07.2020 22:2413.07.2020 22:280:040:00
1013.07.2020 22:1613.07.2020 22:220:060:00
1113.07.2020 17:1513.07.2020 21:354:200:00
1214.07.2020 01:5014.07.2020 02:000:100:00
1314.07.2020 01:4514.07.2020 01:500:050:00
1414.07.2020 01:0514.07.2020 01:200:150:00
1514.07.2020 01:0514.07.2020 01:050:000:00
1614.07.2020 23:1614.07.2020 01:050:00-22:10
1714.07.2020 22:5914.07.2020 23:130:140:00
1814.07.2020 22:3814.07.2020 22:590:210:00
1913.07.2020 23:3213.07.2020 01:050:00-22:26
2013.07.2020 23:0913.07.2020 23:120:030:00
2113.07.2020 21:5613.07.2020 22:300:340:00
Sheet1
Cell Formulas
RangeFormula
C2:C21C2=sbTimeDiff(A2,B2,Early_Hours)+sbTimeDiff(A2,B2,Late_Hours)
D2:D21D2=IF(B2-A2-C2>=0,B2-A2-C2,"-"&TEXT(A2+C2-B2,"[h]:mm"))

MrExcel_Time_Period_in_Shift.xlsm
ABCDE
1ShiftStartEndStartEnd
2Monday17:0024:00
3Tuesday0:002:0017:0024:00
4Wednesday0:002:0017:0024:00
5Thursday0:002:0017:0024:00
6Friday0:002:0017:0024:00
7Saturday0:002:00
8Sunday
9Holidays
Sheet2

Early_Hours = B2:C9
Late_Hours = D2:E9
VBA Code:
Function sbTimeDiff(dtFrom As Date, dtTo As Date, _
    vwh As Variant, _
    Optional vHolidays As Variant, _
    Optional vBreaks As Variant) As Date
'Returns time between dtFrom and dtTo but counts only
'dates and hours given in table vwh: for example
'09:00   17:00  'Monday
'09:00   17:00  'Tuesday
'09:00   17:00  'Wednesday
'09:00   17:00  'Thursday
'09:00   17:00  'Friday
'00:00   00:00  'Saturday
'00:00   00:00  'Sunday
'00:00   00:00  'Holidays
'This table defines hours to count for each day of the
'week (starting with Monday, 2 columns) and for holidays.
'Holidays given in vHolidays overrule week days.
'If you define a break table with break limits greater zero
'then the duration of each break exceeding the applicable
'time for this day will be subtracted from each day's time,
'but only down to the limit time, table needs to be sorted
'by limits in increasing order:
'Break table example
'Limit Duration (title row is not part of the table)
'6:00  0:30
'9:00  0:15
'
'http://sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_VBA/sbTimeDiff/sbtimediff.html
'Reverse("moc.LiborPlus.www") (C) (P) Bernd Plumhoff 21-Mar-2020 PB V1.1
Dim dt2 As Date, dt3 As Date, dt4 As Date, dt5 As Date
Dim i As Long, lTo As Long, lFrom As Long
Dim lWDFrom As Long, lWDTo As Long, lWDi As Long
Dim objHolidays As Object, objBreaks As Object, v As Variant

sbTimeDiff = 0#
If dtTo <= dtFrom Then Exit Function
Set objHolidays = CreateObject("Scripting.Dictionary")
If Not IsMissing(vHolidays) Then
    For Each v In vHolidays
        objHolidays(v.Value) = 1
    Next v
End If
If Not IsMissing(vBreaks) Then
    Set objBreaks = CreateObject("Scripting.Dictionary")
    For i = 1 To vBreaks.Rows.Count
        objBreaks(CDate(vBreaks.Cells(i, 1))) = _
            CDate(vBreaks.Cells(i, 2))
    Next i
End If
lFrom = Int(dtFrom): lWDFrom = Weekday(lFrom, vbMonday)
lTo = Int(dtTo): lWDTo = Weekday(lTo, vbMonday)
If lFrom = lTo Then
    lWDi = lWDTo: If objHolidays(lTo) Then lWDi = 8
    dt3 = lTo + CDate(vwh(lWDi, 2))
    If dt3 > dtTo Then dt3 = dtTo
    dt2 = lTo + CDate(vwh(lWDi, 1))
    If dt2 < dtFrom Then dt2 = dtFrom
    If dt3 > dt2 Then
        dt2 = dt3 - dt2
    Else
        dt2 = 0#
    End If
    If Not IsMissing(vBreaks) Then
        dt2 = sbBreaks(dt2, objBreaks)
    End If
    sbTimeDiff = dt2
    Set objHolidays = Nothing
    Set objBreaks = Nothing
    Exit Function
End If
lWDi = lWDFrom: If objHolidays(lFrom) Then lWDi = 8
If dtFrom - lFrom >= CDate(vwh(lWDi, 2)) Then
    dt2 = 0#
Else
    dt2 = lFrom + CDate(vwh(lWDi, 1))
    If dt2 < dtFrom Then dt2 = dtFrom
    dt2 = lFrom + CDate(vwh(lWDi, 2)) - dt2
    If Not IsMissing(vBreaks) Then
        dt2 = sbBreaks(dt2, objBreaks)
    End If
End If
lWDi = lWDTo: If objHolidays(lTo) Then lWDi = 8
If dtTo - lTo <= CDate(vwh(lWDi, 1)) Then
    dt4 = 0#
Else
    dt4 = lTo + CDate(vwh(lWDi, 2))
    If dt4 > dtTo Then dt4 = dtTo
    dt4 = dt4 - lTo - CDate(vwh(lWDi, 1))
    If Not IsMissing(vBreaks) Then
        dt4 = sbBreaks(dt4, objBreaks)
    End If
End If
dt3 = 0#
For i = lFrom + 1 To lTo - 1
    lWDi = Weekday(i, vbMonday)
    If objHolidays(i) Then lWDi = 8
    dt5 = CDate(vwh(lWDi, 2)) - CDate(vwh(lWDi, 1))
    If Not IsMissing(vBreaks) Then
        dt5 = sbBreaks(dt5, objBreaks)
    End If
    dt3 = dt3 + dt5
Next i
Set objHolidays = Nothing
Set objBreaks = Nothing
sbTimeDiff = dt2 + dt3 + dt4
End Function

Private Function sbBreaks(ByVal dt As Date, objBreaks As Object) As Date
'Subtract break durations from dt as long as it exceeds the break limit,
'but not below break limit.
'Reverse("moc.LiborPlus.www") (C) (P) Bernd Plumhoff 22-Mar-2020 PB V0.992
Dim dtTemp As Date
Dim k As Long
k = 0
Do While k <= UBound(objBreaks.keys)
    If dt > objBreaks.keys()(k) + objBreaks.items()(k) - dtTemp Then
        dt = dt - objBreaks.items()(k)
        dtTemp = dtTemp + objBreaks.items()(k)
    ElseIf dt > objBreaks.keys()(k) - dtTemp Then
        dt = objBreaks.keys()(k) - dtTemp
        Exit Do
    End If
    k = k + 1
Loop
sbBreaks = dt
End Function
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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