Long shot! - Using time stamps, identify how much time was over the weekend

Peptide · Sep 19, 2019

Hello, Trying to use time to figure out if a machines down time falls into the weekend or non working hours.

Down time date stamps we have for a certain machine
Start Date/Time: 9/13/19 10:00 (this is a Friday)
End Date/Time: 9/14/19 9:00 (this is a Saturday)
Non Working time: TBD

If the non working time equals 5pm Friday to 7am Monday, the above example TBD would equal 16:00 hours
- Calculation is 5pm Friday to 9am Saturday = 16:00 hours

How do I do a formula to return the 16 hours?

Any help would be GREATLY appreciated!

shknbk2 · Sep 19, 2019

I don't know about a formula, but here is some code I think will work. It assumes no more than a few days between start and end times where either one or both is during non working time or neither is. If there is a situation where the start time is before the weekend and the end time is after the weekend, then this code will need to be modified to take that into account. Right now, it assumes that if the start and end times are on a weekday, then no non working hours are counted even if it started on 9/12 and ended on 9/17. It can be done, but it will take some more time to make it calculate all of the variables.

Code:

Sub nonWorkingTime()
    Dim d1 As Date
    Dim d2 As Date
    Dim weekendStart As Date
    Dim weekendEnd As Date
    Dim wkdayStart As Integer
    Dim wkdayEnd As Integer
    Dim nonWorkingTime As Double
    Dim one_hour As Double
    
    one_hour = CDbl(#1:00:00 AM#)
    
    d1 = Trim(Mid(Range("A1").Value, InStr(1, Range("A1").Value, ":") + 1))
    d2 = Trim(Mid(Range("A2").Value, InStr(1, Range("A2").Value, ":") + 1))
    wkdayStart = Weekday(d1)
    wkdayEnd = Weekday(d2)
    If wkdayStart = 1 Or wkdayStart = 7 Or _
      (wkdayStart = 2 And TimeSerial(Hour(d1), Minute(d1), Second(d1)) < TimeSerial(7, 0, 0)) Or _
      (wkdayStart = 6 And TimeSerial(Hour(d1), Minute(d1), Second(d1)) >= TimeSerial(17, 0, 0)) Then
        'starts on 1) Sunday
        '          2) Saturday
        '          3) Monday before working hours; or
        '          4) Friday after working hours
        weekendEnd = DateSerial(Year(d1), Month(d1), Day(d1) + 4 - Weekday(d1, vbFriday)) + _
            TimeSerial(7, 0, 0)
        If d2 < weekendEnd Then
            nonWorkingTime = d2 - d1
        Else
            nonWorkingTime = weekendEnd - d1
        End If
    ElseIf wkdayEnd = 1 Or wkdayEnd = 7 Or _
      (wkdayEnd = 2 And TimeSerial(Hour(d2), Minute(d2), Second(d2)) < TimeSerial(7, 0, 0)) Or _
      (wkdayEnd = 6 And TimeSerial(Hour(d2), Minute(d2), Second(d2)) > TimeSerial(17, 0, 0)) Then
        'ends on Monday before working hours or on Saturday or Sunday
        'ends on 1) Sunday
        '        2) Saturday
        '        3) Monday before working hours; or
        '        4) Friday after working hours
        weekendStart = DateSerial(Year(d2), Month(d2), Day(d2) + 1 - Weekday(d2, vbFriday)) + _
            TimeSerial(17, 0, 0)
        If d1 > weekendStart Then
            nonWorkingTime = d2 - d1
        Else
            nonWorkingTime = d2 - weekendStart
        End If
    Else
        'both start and end are completely during working hours
        nonWorkingTime = 0
    End If
    Range("A3").Value = "Non Working time: " & Round(nonWorkingTime / one_hour, 0) & " hours"
End Sub

Also, I'm not sure where your data is located. Based on your request, I used the following scenario:

Book1

A

1

Start Date/Time: 9/13/19 10:00

2

End Date/Time: 9/14/19 9:00

3

Non Working time: 16 hours

Sheet1

Peter_SSs · Sep 20, 2019

What about a couple more examples & calculations/results?
For example
Start: 17-Sep-19 18:00
End: 20-Sep-19 08:00

Start: 13-Sep-19 13:00
End: 16-Sep-19 09:00

Peptide · Sep 23, 2019

Hi Peter and thanks in advance,

Here are some more examples: One key item is the non working start time which for this machine is 17:00 Friday to 7:00 Monday. I hope this helps and again, thanks in advance!
Examples
Start Date/Time: 9/13/19 10:00 (this is a Friday)
End Date/Time: 9/14/19 9:00 (this is a Saturday)
Non Working time: 16
- Calculation is 17:00 Friday to 9:00 Saturday = 16:00 hours

Start Date/Time: 9/12/19 17:00 (this is a Thursday)
End Date/Time: 9/14/19 13:00 (this is a Saturday)
Non Working time: 20 hours
- Calculation is 17:00 Friday to 13:00 Saturday = 20:00 hours

Start Date/Time: 9/13/19 18:00 (this is a Friday)
End Date/Time: 9/16/19 7:00 (this is a Monday)
Non Working time: 61 hours
- Calculation is 18:00 Friday to only 7:00 Monday as that is start the working time again 61 hours

Peter_SSs · Sep 24, 2019

Peptide said:
Here are some more examples:

Thanks. See if this does what you want.
I have included the day name in my date formatting just to help me visualise what was happening.
Of course my dates are in d/m/y format.

Excel Workbook

A

B

C

1

Start

End

Weekend

2

Fri 13/09/2019 10:00

Sat 14/09/2019 9:00

16

3

Thu 12/09/2019 17:00

Sat 14/09/2019 13:00

20

4

Fri 13/09/2019 18:00

Mon 16/09/2019 7:00

61

5

Sat 14/09/2019 20:00

Tue 17/09/2019 4:00

35

6

Wed 11/09/2019 13:00

Tue 17/09/2019 11:00

62

7

Fri 13/09/2019 0:00

Mon 16/09/2019 1:00

56

8

Sun 15/09/2019 11:00

Sun 15/09/2019 11:30

0.5

9

Sat 14/09/2019 23:12

Sun 15/09/2019 0:06

0.9

10

Thu 12/09/2019 17:00

Fri 13/09/2019 10:00

0

Sheet1

Peptide · Sep 24, 2019

Brilliant!!

This is amazing and all my testing works so far. I love to learn for next time and trying to figure this formula out

What does the >5 do?
What does the +7 do?
What does the 16 in WEEKDAY(A2,16) do?
What does the +17/24 do?
Assume that the WEEKDAY is a built in function?

Depending on the above answers, if I want to change the weekend time from Friday 5pm to 4pm and Monday 7am to 8am - where would I do this?

Thanks so much in advance - this is great!!

Bob

=MAX(24*(IF(WEEKDAY(B2,2)>5,B2,MIN(B2,INT(B2)-WEEKDAY(B2,3)+7/24))-IF(WEEKDAY(A2,2)>5,A2,MAX(A2,INT(A2)+7-WEEKDAY(A2,16)+17/24))),0)

Peptide · Sep 24, 2019

Peter -
I have one other question on your output data column C. On line 8 for example, output is .5 which means 30 minutes. I have tried to format the cells to get 00:30 (HH:MM) but don't get that results. Any thought?

Thanks, Bob

Peter_SSs · Sep 24, 2019

Peptide said:
Brilliant!!

Glad you are happy with it.

Peptide said:
What does the >5 do?
What does the +7 do?
What does the 16 in WEEKDAY(A2,16) do?
What does the +17/24 do?
Assume that the WEEKDAY is a built in function?

Yes, WEEKDAY is a built-in function (actually a group of functions given that the second argument can change). You need to do some research in Help/Google to learn more about the WEEKDAY function and that will help answer some of your questions.

Basically my formula ..
a) looks at the end time and
- if it is a weekend leaves it alone
- if it is a weekday it adjusts the day back to the Monday and then uses the 7am time or the actual time in column B if that date was already a Monday and the time was before 7am.

b) looks at the start time and
- if it is a weekend leaves it alone
- if it is a weekday it adjusts the day forward to the Friday and then uses the 5pm time or the actual time in column A if that date was already a Friday and the time was after 5pm.

c) Subtracts those two numbers and multiplies by 24 to give hours.

d) Has another check in case the Start/End does not overlap the weekend time at all like in row 10 of my sample.

Peptide said:
Depending on the above answers, if I want to change the weekend time from Friday 5pm to 4pm and Monday 7am to 8am - where would I do this?

That is what the 7/24 and 17/24 in my formula are about.
Excel processes dates as whole numbers and times within the day as a fraction of 1. So 7/24 is 7/24 of a day and that gives 7am or 7:00 and 17/24 = 17:00 or 5pm
So 4pm (16:00) would use 16/24 instead of 17/24 etc

Peptide said:
I have one other question on your output data column C. On line 8 for example, output is .5 which means 30 minutes. I have tried to format the cells to get 00:30 (HH:MM) but don't get that results. Any thought?

Thanks, Bob

We need to get rid of the 24*(...) so formula as shown below and format the column as [hh]:mm

Note that you must include those square brackets or the hours that are > 24 will display incorrectly.

Excel Workbook

A

B

C

1

Start

End

Weekend

2

Fri 13/09/2019 10:00

Sat 14/09/2019 9:00