uday
Member
HI,
Please help below mentioned vba code. I am trying to compare the time difference between now and other time mentioned in different column.
>>> use code - tags <<<
This is giving me the #value error.
Please help on this code.
Please help below mentioned vba code. I am trying to compare the time difference between now and other time mentioned in different column.
>>> use code - tags <<<
Code:
Function ShiftTimeDifference(StartTime As Date) As Double
Dim EndTime As Date
Dim TotalHours As Double
Dim CurrentDate As Date
Dim ShiftStart As Date
Dim ShiftEnd As Date
Dim DayStart As Date
Dim DayEnd As Date
EndTime = Now() ' Use current time as the end time
TotalHours = 0
CurrentDate = Application.WorksheetFunction.DateValue(StartTime)
DayStart = Application.WorksheetFunction.DateValue(CurrentDate) + TimeValue("08:00:00")
DayEnd = Application.WorksheetFunction.DateValue(CurrentDate) + TimeValue("20:00:00")
' Adjust StartTime to fit within the shift window
If StartTime < DayStart Then StartTime = DayStart
' Loop through each day
Do While StartTime < EndTime
ShiftStart = Application.WorksheetFunction.DateValue(StartTime) + TimeValue("08:00:00")
ShiftEnd = Application.WorksheetFunction.DateValue(StartTime) + TimeValue("20:00:00")
' Adjust end time if it falls after the shift end
If EndTime > ShiftEnd Then EndTime = ShiftEnd
' Calculate the hours within the shift window
If StartTime < ShiftEnd Then
If EndTime > ShiftStart Then
TotalHours = TotalHours + (Application.WorksheetFunction.Min(EndTime, ShiftEnd) - Application.WorksheetFunction.Max(StartTime, ShiftStart)) * 24
End If
End If
' Move to the next day
StartTime = Application.WorksheetFunction.DateValue(StartTime) + 1 + TimeValue("08:00:00")
EndTime = Now() ' Ensure EndTime remains current time
Loop
ShiftTimeDifference = TotalHours
End Function
This is giving me the #value error.
Please help on this code.
Last edited by a moderator: