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: 104
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

Hi Sulprobil,

Thank you so much for your help, I tired running the code but i get an error pop up as "expected end sub error" . I tried googling it and work on the code but I am not able to proceed.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You can download and use my file here at your own risk (but I am using an up-to-date virus scanner):

If it does not work, you can press ALT + F11, insert a new (general, not class) module, and copy the VBA code in there.

If all of this does not help, I cannot help you. You would need a friend next to you at your computer to set it up for you.
 
Upvote 0
You can download and use my file here at your own risk (but I am using an up-to-date virus scanner):

If it does not work, you can press ALT + F11, insert a new (general, not class) module, and copy the VBA code in there.

If all of this does not help, I cannot help you. You would need a friend next to you at your computer to set it up for you.


Hi Sulprobil,

Thanks a lot , I did download the excel sheet that you had shared and its working but there seems to be a problem in the calculation.

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. - Table 1
I did a temporary solution by adding +1 to the dates when an event falls between 12am to 5am - Table 2

So there are multiple shift shifts, how do i add them to the vba code?
7:00 am IST to 4:00 pm IST
8:00 am IST to 5:00 pm IST
10:00 am IST to 7:00 pm IST
2:00 PM IST to 11:00 pm IST

Table 1

MrExcel_Time_Period_in_Shift(1).xlsm
ABCD
1start_datetimeend_datetimeInsideOutside
27/13/2020 1:557/13/2020 1:560:000:01
37/13/2020 1:317/13/2020 1:310:000:00
47/13/2020 1:117/13/2020 1:110:000:00
57/13/2020 1:107/13/2020 1:110:000:01
67/13/2020 23:547/13/2020 1:100:00-22:43
77/13/2020 23:137/13/2020 23:270:140:00
87/13/2020 22:297/13/2020 22:310:020:00
97/13/2020 22:247/13/2020 22:280:040:00
107/13/2020 22:167/13/2020 22:220:060:00
117/13/2020 17:157/13/2020 21:354:200:00
127/14/2020 1:507/14/2020 2:000:100:00
137/14/2020 1:457/14/2020 1:500:050:00
147/14/2020 1:057/14/2020 1:200:150:00
157/14/2020 1:057/14/2020 1:050:000:00
167/14/2020 23:167/14/2020 1:050:00-22:10
177/14/2020 22:597/14/2020 23:130:140:00
187/14/2020 22:387/14/2020 22:590:210:00
197/13/2020 23:327/13/2020 1:050:00-22:26
207/13/2020 23:097/13/2020 23:120:030:00
217/13/2020 21:567/13/2020 22:300:340:00
Sheet1


Table 2

MrExcel_Time_Period_in_Shift(1).xlsm
ABCD
1start_datetimeend_datetimeInsideOutside
27/13/2020 1:557/13/2020 1:560:000:01
37/13/2020 1:317/13/2020 1:310:000:00
47/13/2020 1:117/13/2020 1:110:000:00
57/13/2020 1:107/13/2020 1:110:000:01
67/13/2020 23:547/14/2020 1:101:160:00
77/13/2020 23:137/13/2020 23:270:140:00
87/13/2020 22:297/13/2020 22:310:020:00
97/13/2020 22:247/13/2020 22:280:040:00
107/13/2020 22:167/13/2020 22:220:060:00
117/13/2020 17:157/13/2020 21:354:200:00
127/14/2020 1:507/14/2020 2:000:100:00
137/14/2020 1:457/14/2020 1:500:050:00
147/14/2020 1:057/14/2020 1:200:150:00
157/14/2020 1:057/14/2020 1:050:000:00
167/14/2020 23:167/15/2020 1:051:490:00
177/14/2020 22:597/14/2020 23:130:140:00
187/14/2020 22:387/14/2020 22:590:210:00
197/13/2020 23:327/15/2020 1:0510:3315:00
207/13/2020 23:097/13/2020 23:120:030:00
217/13/2020 21:567/13/2020 22:300:340:00
Sheet1
 
Upvote 0
Write into D2:
VBA Code:
=IF(B2-A2-C2>=0,B2-A2-C2,B2+1-A2-C2)
and insert a new column which defines the shift#,, then replace Early_Hours (and similarly Late_Hours) by
Code:
OFFSET(Early_Hours,(Shift# - 1)*9)
and write down all shift times in Sheet2 underneath each other.
 
Upvote 0
MrExcel_Time_Period_in_Shift2.xlsm
ABCDE
1start_datetimeend_datetimeShift #InsideOutside
213.07.2020 01:5513.07.2020 01:5610:000:01
313.07.2020 16:5914.07.2020 02:0119:000:02
413.07.2020 23:5413.07.2020 01:1011:160:00
513.07.2020 00:0014.07.2020 00:0017:0017:00
613.07.2020 01:5513.07.2020 01:5620:000:01
713.07.2020 16:5914.07.2020 02:0120:009:02
813.07.2020 23:5413.07.2020 01:1020:001:16
913.07.2020 00:0014.07.2020 00:0029:0015:00
1013.07.2020 01:5513.07.2020 01:5630:000:01
1113.07.2020 16:5914.07.2020 02:0130:019:01
1213.07.2020 23:5413.07.2020 01:1030:001:16
1313.07.2020 00:0014.07.2020 00:0039:0015:00
1413.07.2020 01:5513.07.2020 01:5640:000:01
1513.07.2020 16:5914.07.2020 02:0142:017:01
1613.07.2020 23:5413.07.2020 01:1040:001:16
1713.07.2020 00:0014.07.2020 00:0049:0015:00
1813.07.2020 01:5513.07.2020 01:5650:000:01
1913.07.2020 16:5914.07.2020 02:0156:013:01
2013.07.2020 23:5413.07.2020 01:1050:001:16
2113.07.2020 00:0014.07.2020 00:0059:0015:00
Sheet1
Cell Formulas
RangeFormula
D2:D21D2=sbTimeDiff(A2,B2+(A2>B2),OFFSET(Early_Hours,($C2-1)*9,0))+sbTimeDiff(A2,B2+(A2>B2),OFFSET(Late_Hours,($C2-1)*9,0))
E2:E21E2=B2-A2-D2+(A2>B2)

Note: I am not a friend of this (A2>B2) trick. Normally you need to clean your data before you process it.

MrExcel_Time_Period_in_Shift2.xlsm
ABCDE
1Shift 1StartEndStartEnd
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
10Shift 2StartEndStartEnd
11Monday7:0016:00
12Tuesday7:0016:00
13Wednesday7:0016:00
14Thursday7:0016:00
15Friday7:0016:00
16Saturday
17Sunday
18Holidays
19Shift 3StartEndStartEnd
20Monday8:0017:00
21Tuesday8:0017:00
22Wednesday8:0017:00
23Thursday8:0017:00
24Friday8:0017:00
25Saturday
26Sunday
27Holidays
28Shift 4StartEndStartEnd
29Monday10:0019:00
30Tuesday10:0019:00
31Wednesday10:0019:00
32Thursday10:0019:00
33Friday10:0019:00
34Saturday
35Sunday
36Holidays
37Shift 5StartEndStartEnd
38Monday14:0023:00
39Tuesday14:0023:00
40Wednesday14:0023:00
41Thursday14:0023:00
42Friday14:0023:00
43Saturday
44Sunday
45Holidays
Sheet2


The file is stored here:

 
Upvote 0
MrExcel_Time_Period_in_Shift2.xlsm
ABCDE
1start_datetimeend_datetimeShift #InsideOutside
213.07.2020 01:5513.07.2020 01:5610:000:01
313.07.2020 16:5914.07.2020 02:0119:000:02
413.07.2020 23:5413.07.2020 01:1011:160:00
513.07.2020 00:0014.07.2020 00:0017:0017:00
613.07.2020 01:5513.07.2020 01:5620:000:01
713.07.2020 16:5914.07.2020 02:0120:009:02
813.07.2020 23:5413.07.2020 01:1020:001:16
913.07.2020 00:0014.07.2020 00:0029:0015:00
1013.07.2020 01:5513.07.2020 01:5630:000:01
1113.07.2020 16:5914.07.2020 02:0130:019:01
1213.07.2020 23:5413.07.2020 01:1030:001:16
1313.07.2020 00:0014.07.2020 00:0039:0015:00
1413.07.2020 01:5513.07.2020 01:5640:000:01
1513.07.2020 16:5914.07.2020 02:0142:017:01
1613.07.2020 23:5413.07.2020 01:1040:001:16
1713.07.2020 00:0014.07.2020 00:0049:0015:00
1813.07.2020 01:5513.07.2020 01:5650:000:01
1913.07.2020 16:5914.07.2020 02:0156:013:01
2013.07.2020 23:5413.07.2020 01:1050:001:16
2113.07.2020 00:0014.07.2020 00:0059:0015:00
Sheet1
Cell Formulas
RangeFormula
D2:D21D2=sbTimeDiff(A2,B2+(A2>B2),OFFSET(Early_Hours,($C2-1)*9,0))+sbTimeDiff(A2,B2+(A2>B2),OFFSET(Late_Hours,($C2-1)*9,0))
E2:E21E2=B2-A2-D2+(A2>B2)

Note: I am not a friend of this (A2>B2) trick. Normally you need to clean your data before you process it.

MrExcel_Time_Period_in_Shift2.xlsm
ABCDE
1Shift 1StartEndStartEnd
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
10Shift 2StartEndStartEnd
11Monday7:0016:00
12Tuesday7:0016:00
13Wednesday7:0016:00
14Thursday7:0016:00
15Friday7:0016:00
16Saturday
17Sunday
18Holidays
19Shift 3StartEndStartEnd
20Monday8:0017:00
21Tuesday8:0017:00
22Wednesday8:0017:00
23Thursday8:0017:00
24Friday8:0017:00
25Saturday
26Sunday
27Holidays
28Shift 4StartEndStartEnd
29Monday10:0019:00
30Tuesday10:0019:00
31Wednesday10:0019:00
32Thursday10:0019:00
33Friday10:0019:00
34Saturday
35Sunday
36Holidays
37Shift 5StartEndStartEnd
38Monday14:0023:00
39Tuesday14:0023:00
40Wednesday14:0023:00
41Thursday14:0023:00
42Friday14:0023:00
43Saturday
44Sunday
45Holidays
Sheet2


The file is stored here:


Hi
Write into D2:
VBA Code:
=IF(B2-A2-C2>=0,B2-A2-C2,B2+1-A2-C2)
and insert a new column which defines the shift#,, then replace Early_Hours (and similarly Late_Hours) by
Code:
OFFSET(Early_Hours,(Shift# - 1)*9)
and write down all shift times in Sheet2 underneath each other.

Hi Sulprobil,

Thank you so much for your help this worked. I really appreciate that you took time to help me out. I will let you know if i face any other problem.
 
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

View attachment 19153

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.

Hi Eric,

Thank you so much to you too for helping me out. I did find a way to work around the problem that I came across. Hope I can learn a lot more in excel with this experience.
 
Upvote 0
I'm glad you got your problem resolved. I had some other thoughts on it, but I wanted to see if Sulprobil's approach would work. In any case, if you have any more questions, come back and someone here will help you out. :)
 
Upvote 0

Forum statistics

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

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