Total amount of time spent in specific time period across multiple days

Question

I have been having difficulty with the following problem. ~~I have the following excel spreadsheet: link~~

I need an Excel formula to calculate the total amount of time spent between a specific time period i.e 20:00 and 05:00, between a start date and end date for example:

_{(Rephrased for Clarity:)}

TIME SPAN:
Start_TimeStanmp 2018/04/04 00:12:53
End_TimeStamp 2018/04/16 13:24:04

DAILY SHIFT:
"Shift" Start 20:00:00
"Shift" End 05:00:00

What are the total hours of TIME SPAN that fall into DAILY SHIFTS?

does the "specific time period" always cross midnight like that? — ashleedawg
It does unfortunately which is why I am having difficulty, It is always from 20:00 to 05:00 — Michael Wilkinson
What's wrong with subtracting the START_TIMESTAMP from the END_TIMESTAMP and formatting the result as [hh]:mm:ss ? — user4039065
@jeeped ...because that doesn't count only the hours that fall between 20:00 and 5:00 — ashleedawg
@ashleedawg I agree with you. You feel more responsible for the question than the OP because of the effort making the question more clear. I am thinking of another solution that takes the shifting of summertime (aka daily saving time) into account. But it is a waste of time if the OP doesn't respond. — ffonz

ashleedawg ashleedawg · Accepted Answer · 2018-04-21T12:26:11

There may be an easier way but this UDF should do the trick:

Function hoursBetween(startDT As Date, stopDT As Date, timeStart As Date, timeStop As Date) As Single

    Dim dt As Date, thisStart As Date, thisStop As Date
    For dt = Int(startDT) To Int(stopDT) + IIf(timeStop < timeStart, -1, 0)

        thisStart = timeStart
        thisStop = timeStop

        If timeStart < timeStop Then
            Select Case dt
                Case Int(startDT) 'its the 1st day of the range
                    thisStart = IIf(startDT - Int(startDT) > timeStart Or (timeStart < timeStop), startDT - Int(startDT), timeStart)
                Case Int(stopDT) 'its the last day of the range
                    thisStop = IIf(stopDT - Int(stopDT) < timeStop, stopDT - Int(stopDT), timeStop)
            End Select
            Debug.Print dt & " : " & thisStart & " to " & thisStop
        Else
            'the day crosses midnight
            Select Case dt
                Case Int(startDT) 'its the 1st day of the range
                    thisStart = IIf(startDT - Int(startDT) > timeStart, timeStart, startDT - Int(startDT))
                Case Int(stopDT)  'its the last day of the range
                    thisStop = IIf(stopDT - Int(stopDT) > timeStop, timeStop, stopDT - Int(stopDT))
            End Select
        End If

        thisStart = thisStart + dt
        thisStop = thisStop + dt
        If thisStop < thisStart Then thisStop = thisStop + 1
        hoursBetween = hoursBetween + ((thisStop - thisStart) * 24)
        Debug.Print dt & " : " & thisStart & " to " & thisStop, "Day hrs: " & ((thisStop - thisStart) * 24)
    Next dt
End Function

To test it with your example:

Sub test()
    Debug.Print hoursBetween(#2018/04/04 00:12:53#, #2018/04/16 13:24:04#, #20:00#, #05:00#)
End Sub

Note that "not knowing" if the shift time will always cross midnight like in your example, required double the amount of code... (although there are likely ways to streamline it.)

04-04 : 04-04 12:12:53 to 04-04 05:00:00           Day hrs: 4.79
04-05 : 04-05 20:00:00 to 04-06 05:00:00           Day hrs: 9
04-06 : 04-06 20:00:00 to 04-07 05:00:00           Day hrs: 9
04-07 : 04-07 20:00:00 to 04-08 05:00:00           Day hrs: 9
04-08 : 04-08 20:00:00 to 04-09 05:00:00           Day hrs: 9
04-09 : 04-09 20:00:00 to 04-10 05:00:00           Day hrs: 9
04-10 : 04-10 20:00:00 to 04-11 05:00:00           Day hrs: 9
04-11 : 04-11 20:00:00 to 04-12 05:00:00           Day hrs: 9
04-12 : 04-12 20:00:00 to 04-13 05:00:00           Day hrs: 9
04-13 : 04-13 20:00:00 to 04-14 05:00:00           Day hrs: 9
04-14 : 04-14 20:00:00 to 04-15 05:00:00           Day hrs: 9
04-15 : 04-15 20:00:00 to 04-16 05:00:00           Day hrs: 9
                                                   Total: 103.7853

Total amount of time spent in specific time period across multiple days

What are the total hours of TIME SPAN that fall into DAILY SHIFTS?

2 Answers

Step 1

Step 2

Step 3

Add it up