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

P

Peptide

Board Regular
Joined
Apr 12, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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
1Start Date/Time: 9/13/19 10:00
2End Date/Time: 9/14/19 9:00
3Non Working time: 16 hours
Sheet1
 
Upvote 0
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
 
Last edited:
Upvote 0
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

 
Upvote 0
Peptide said:
Here are some more examples:
Click to expand...
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
ABC
1StartEndWeekend
2Fri 13/09/2019 10:00Sat 14/09/2019 9:0016
3Thu 12/09/2019 17:00Sat 14/09/2019 13:0020
4Fri 13/09/2019 18:00Mon 16/09/2019 7:0061
5Sat 14/09/2019 20:00Tue 17/09/2019 4:0035
6Wed 11/09/2019 13:00Tue 17/09/2019 11:0062
7Fri 13/09/2019 0:00Mon 16/09/2019 1:0056
8Sun 15/09/2019 11:00Sun 15/09/2019 11:300.5
9Sat 14/09/2019 23:12Sun 15/09/2019 0:060.9
10Thu 12/09/2019 17:00Fri 13/09/2019 10:000
Sheet1
 
Last edited:
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
Peptide said:
Brilliant!!
Click to expand...
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?
Click to expand...
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?
Click to expand...
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
Click to expand...
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
ABC
1StartEndWeekend
2Fri 13/09/2019 10:00Sat 14/09/2019 9:0016:00
3Thu 12/09/2019 17:00Sat 14/09/2019 13:0020:00
4Fri 13/09/2019 18:00Mon 16/09/2019 7:0061:00
5Sat 14/09/2019 20:00Tue 17/09/2019 4:0035:00
6Wed 11/09/2019 13:00Tue 17/09/2019 11:0062:00
7Fri 13/09/2019 0:00Mon 16/09/2019 1:0056:00
8Sun 15/09/2019 11:00Sun 15/09/2019 11:3000:30
9Sat 14/09/2019 23:12Sun 15/09/2019 0:0600:54
10Thu 12/09/2019 17:00Fri 13/09/2019 10:0000:00
Sheet1 (2)
 
Upvote 0
Thanks so much Peter,
I appreciate you helping me learn this new data/time/weekend formula. I think I have a better understanding of what the formula is doing and have looked at Google help as well which help.

Having said that, this process of figuring out down time over the weekend is tougher than I thought!! I am going to press my luck with you for an additional help if I may...

Current formula
=MAX(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)

Logic I need to add to current formula if possible
If C2 = 'Running" and B2 is between 5pm FRI and 7am MON, then return the HH:MM from B2 to 7:00 MON, otherwise do the current formula

Am I a dreamer?
 
Upvote 0
Peptide said:
Logic I need to add to current formula if possible
If C2 = 'Running" and B2 is between 5pm FRI and 7am MON, then return the HH:MM from B2 to 7:00 MON, otherwise do the current formula
Click to expand...
Is column B still the 'End' time?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Hours Between 2 Dates Excluding Non-Business Hours ONLY
Replies
7
Views
732
felixstraube
F
P
  • Question Question
How to subtract 2 dates and times from each other excl weekends, holidays and only considering working hours during the day?
Replies
1
Views
200
Micron
M
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
670
Asbestos_Jen
Asbestos_Jen
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
1K
felixstraube
F
C
  • Question Question
Ordinary Hours or Penalty Shift
Replies
0
Views
420
ChetanPuri
C

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

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