Hello All!
This is my 1st post here. After looong brainstorming I decided to try forums with my issue...
I need to create a formula in VBA which would substract dates (that is easy part).
But...there are few contidions:
1) I need to include only working days (worksheetfunction.networkdays)
2) I need to remember, that every full day is 8.50 hours long
3) 1st day finishes at 5:00:00 PM
4) last day starts at 8:30:00 AM
What I need to is to count the total time of an order from when it is in the system until it's completed, there're few steps:
1) count worksheetfunction.networkdays, substract 2 (1st and last day)
2) count number of hours for 1st day for example: 5:00:00 PM - 1/23/2019 10:38:51 AM(this is the format in file)
3) count number of hours for last day for exampl: 1/30/2019 12:59:32 PM - 8:30:00
4) summarise 1st day hours + last day hours + full days hours (8.5 hour per day as mentioned).
This is what I was trying to do...it works if there're no integers, for example 34 hours or so, but in case of 8.5 hour I'm struggling to add it, to the sum of the 1st and last day...any help would be much appreciated.
Code(it's not finished, I afraid not even in half):
Function TotalHours(Start As Date, EndT As Date)
Dim FirstDay, LastDay As Long
NetworkD = WorksheetFunction.NetworkDays(Start, EndT) - 2
FullDays = NetworkD * 8.5
If (TimeValue(Start) > TimeValue("5:00:00 PM")) Then StartDiff = 0 Else StartDiff = TimeValue("5:00:00 PM") - TimeValue(Start)
'MsgBox Hour(StartDiff) & ":" & Minute(StartDiff) & ":" & Second(StartDiff)
If (TimeValue("8:30:00 AM") < TimeValue(EndT)) Then EndDiff = 0 Else EndDiff = TimeValue(EndT) - TimeValue("8:30:00 AM")
'MsgBox Hour(EndDiff) & ":" & Minute(EndDiff) & ":" & Second(EndDiff)
Select Case NetworkD
Case Is >= 2
FirstAndLast = Left(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
Total = FullDays + FirstAndLast & ":" & Mid(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 4, 2) _
& ":" & Right(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
TotalHours = Total
Case Is = 1
'FirstAndLast = Left(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
'Total = FullDays + FirstAndLast & ":" & Mid(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 4, 2) _
& ":" & Right(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
'TotalHours = Total
'MsgBox Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff)
'MsgBox TimeSerial(8, 30, 0)
'MsgBox TimeValue(FullDays)
Case Else
TotalHours = ""
End Select
End Function
This is my 1st post here. After looong brainstorming I decided to try forums with my issue...
I need to create a formula in VBA which would substract dates (that is easy part).
But...there are few contidions:
1) I need to include only working days (worksheetfunction.networkdays)
2) I need to remember, that every full day is 8.50 hours long
3) 1st day finishes at 5:00:00 PM
4) last day starts at 8:30:00 AM
What I need to is to count the total time of an order from when it is in the system until it's completed, there're few steps:
1) count worksheetfunction.networkdays, substract 2 (1st and last day)
2) count number of hours for 1st day for example: 5:00:00 PM - 1/23/2019 10:38:51 AM(this is the format in file)
3) count number of hours for last day for exampl: 1/30/2019 12:59:32 PM - 8:30:00
4) summarise 1st day hours + last day hours + full days hours (8.5 hour per day as mentioned).
This is what I was trying to do...it works if there're no integers, for example 34 hours or so, but in case of 8.5 hour I'm struggling to add it, to the sum of the 1st and last day...any help would be much appreciated.
Code(it's not finished, I afraid not even in half):
Function TotalHours(Start As Date, EndT As Date)
Dim FirstDay, LastDay As Long
NetworkD = WorksheetFunction.NetworkDays(Start, EndT) - 2
FullDays = NetworkD * 8.5
If (TimeValue(Start) > TimeValue("5:00:00 PM")) Then StartDiff = 0 Else StartDiff = TimeValue("5:00:00 PM") - TimeValue(Start)
'MsgBox Hour(StartDiff) & ":" & Minute(StartDiff) & ":" & Second(StartDiff)
If (TimeValue("8:30:00 AM") < TimeValue(EndT)) Then EndDiff = 0 Else EndDiff = TimeValue(EndT) - TimeValue("8:30:00 AM")
'MsgBox Hour(EndDiff) & ":" & Minute(EndDiff) & ":" & Second(EndDiff)
Select Case NetworkD
Case Is >= 2
FirstAndLast = Left(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
Total = FullDays + FirstAndLast & ":" & Mid(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 4, 2) _
& ":" & Right(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
TotalHours = Total
Case Is = 1
'FirstAndLast = Left(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
'Total = FullDays + FirstAndLast & ":" & Mid(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 4, 2) _
& ":" & Right(Format(Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff), "HH:MM:SS"), 2)
'TotalHours = Total
'MsgBox Hour(StartDiff + EndDiff) & ":" & Minute(StartDiff + EndDiff) & ":" & Second(StartDiff + EndDiff)
'MsgBox TimeSerial(8, 30, 0)
'MsgBox TimeValue(FullDays)
Case Else
TotalHours = ""
End Select
End Function