Time Difference Between Now (End Time) And Start Time Within Shift Timing

[uday]

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 <<<

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.

 

[vletm]

uday​

You could use something same as with Your previous thread.
Please, reread Forum Rules
especially How to get the Best Results at Chandoo.org