Brtdku

One of our KPIs is hours between arrival and departure, however the hours we are not working do not count against us. I am aware of networkdays and networkdays.intl and initially used it on one of our files

IF(IF(INDIRECT("$D"&ROW())<>"",ROUNDDOWN(IF(INDIRECT("$D"&ROW())>INDIRECT("$A"&ROW()),0,(NETWORKDAYS(INDIRECT("$D"&ROW()),INDIRECT("$A"&ROW()),$W$2:$W$100)-NETWORKDAYS(INDIRECT("$D"&ROW()),INDIRECT("$D"&ROW()),$W$2:$W$100)*MOD(INDIRECT("$D"&ROW()),1)-NETWORKDAYS(INDIRECT("$A"&ROW()),INDIRECT("$A"&ROW()),$W$2:$W$100)*(1-MOD(INDIRECT("$A"&ROW()),1)))*24),0),"")>24,"X","")

where $A is start time and $D is end time, and W2-W100 is holidays.

However this doesn't work because our "open" hours that count against us are Monday 6am to Friday 11pm and Saturday 7am to 2pm, but we have records that also cover the range of Friday 11pm to Saturday 7am that should show as zero, as well as potentially Saturday 2pm to 10pm. We also have holidays off however on those holidays third shift finishes at 6am that day and first starts at 6am the next, so some of the numbers get screwed up when using networkdays.

I've been trying off and on for months to figure this out and nothing is working.

Originally we had an access database that did as simple if week(start) > week(end) then time = end - start - 2. Obviously that doesn't fit our KPI requirement.

I also tried

    Function customTime(startTime As Date, endTime As Date, holidays As Range) As Date



Dim returnTime



returnTime = endTime - startTime - (2 * (Application.WeekNum(endTime, 2) - Application.WeekNum(startTime, 2)))

    If Application.Weekday(startTime, 2) = 6 Then returnTime = returnTime + startTime - Application.RoundDown(startTime, 0)

    If Application.Weekday(startTime, 2) = 7 Then returnTime = returnTime + startTime - Application.RoundDown(startTime, 0) + 1



For Each hols In holidays

    If startTime < hols And endTime > hols + 1 Then returnTime = returnTime - 1

Next hols

CustomTime = returnTime



End Function

which basically does the same thing as network days but accounts for work on the weekends (I think). I did eventually have to fix the new year bug.

returnTime = endTime - startTime - (2 * ((Year(endTime) * 52) + Application.WeekNum(endTime, 2) - (Year(startTime) * 52) - Application.WeekNum(startTime, 2)))

This was sufficient for a while but now we're looking to get more accurate data on these and want to exclude all of our exact hours. I've changed it to use open and close timestamps in two columns and in the process of adjusting the program to actually do the math here, I'm screwing up the syntax (I think) and causing it to error out and occasionally crash excel entirely.

Function CTime2(startTime As Date, endTime As Date, hoursrange As Range) As Date



Dim returnTime

returnTime = endTime - startTime



Dim hours As Variant

hours = hoursrange.Value



For i = LBound(hours, 1) + 1 To UBound(hours, 1)

    If startTime < hours(i, 1) And endTime < hours(i, 1) Then

        Exit For

    ElseIf startTime > hours(i, 2) And endTime > hours(i, 2) Then

        Next i

    ElseIf startTime > hours(i, 1) And endTime < hours(i, 2) Then

        Exit For

    ElseIf startTime < hours(i - 1, 2) And endTime > hours(i, 1) Then

        returnTime = returnTime - (hours(i, 1) - hours(i - 1, 1))

    End If

Next i

CTime2 = returnTime

End Function

It's failed because it can't find a for loop for the exit or next, can't find an if loop for the else, can't find a do for a loop while (when I was using that as a continue). I don't know enough about vba to figure out what i'm doing wrong, or even if i'm on the right path here.