9am to 6pm elapsed time

[blossomthe2nd]

Hi Guys

I totally plagerised this code to calculate how much time has elapsed between a startdate and starttime to enddate and endtime , counting the day as a 9 to 6 Working day. -

It works perfectly for everyday except when the Month changes and then it does not work, so I guess I need to change the code -- I dont even know where to start ,

Can anyone PLEASE PLEASE Help me ?

Thanks

A

=nhw(C8 + B8,P8+O8,"09:00","18:00",0)

Function nhw(StartingDateTime As Date, EndingDateTime As Date, HourStart As Date, _
        HourEnd As Date, ReturnType As Integer) As Variant
        
        Dim TotalNetHoursWorked
        Dim ThisDayEnd As Date
        Dim ThisDayBegin As Date
        Dim Cntr As Integer
Application.Volatile
If StartingDateTime = 0 Then Exit Function
If EndingDateTime = 0 Then Exit Function
If Format(StartingDateTime, "DDDD") = "Friday" And Hour(StartingDateTime) > 17 Then
    StartingDateTime = Format(DateAdd("d", 3, StartingDateTime), "MM/DD/YY") & " 09:00 AM"
ElseIf Format(StartingDateTime, "DDDD") = "Saturday" Then
    StartingDateTime = Format(DateAdd("d", 2, StartingDateTime), "MM/DD/YY") & " 09:00 AM"
ElseIf Format(StartingDateTime, "DDDD") = "Sunday" Then
    StartingDateTime = Format(DateAdd("d", 1, StartingDateTime), "MM/DD/YY") & " 09:00 AM"
End If
If DateDiff("d", StartingDateTime, EndingDateTime) > 1826 Then
    MsgBox "Out of range - Greater than five years."
    nhw = "Invalid Time"
    Exit Function
End If
ThisDayEnd = Month(StartingDateTime) & "/" & Day(StartingDateTime) & "/" & _
             Year(StartingDateTime)
ThisDayEnd = ThisDayEnd + HourEnd
If ThisDayEnd > EndingDateTime Then
    TotalNetHoursWorked = DateDiff("n", StartingDateTime, EndingDateTime)
    GoTo AllDone
End If
TotalNetHoursWorked = DateDiff("n", StartingDateTime, ThisDayEnd)
For Cntr = 1 To 1826
    ThisDayBegin = Month(StartingDateTime + Cntr) & "/" & Day(StartingDateTime + Cntr) & "/" & _
                 Year(StartingDateTime + Cntr) & Format(HourStart, " HH:MM")
    ThisDayEnd = ThisDayBegin + (HourEnd - HourStart)
    If ThisDayBegin > EndingDateTime Then Exit For
    If ThisDayEnd > EndingDateTime Then
        If Format(ThisDayEnd, "MMMM") = "Saturday" Then Exit For
        TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, EndingDateTime)
        Exit For
    End If
    If Format(ThisDayEnd, "DDDD") <> "Saturday" And _
       Format(ThisDayEnd, "DDDD") <> "Sunday" Then
    TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, ThisDayEnd)
    End If
Next
AllDone:
Debug.Print TotalNetHoursWorked Mod 60
If TotalNetHoursWorked > 1440 Then
    nhw = Int(TotalNetHoursWorked / 60) & ":" & TotalNetHoursWorked Mod 60 & ":00"
Else: nhw = 0.000694444 * TotalNetHoursWorked
End If
End Function

 

[AlphaFrog]

Hi Guys

I totally plagerised this code to calculate how much time has elapsed between a startdate and starttime to enddate and endtime , counting the day as a 9 to 6 Working day. -

It works perfectly for everyday except when the Month changes and then it does not work, so I guess I need to change the code -- I dont even know where to start ,

Can anyone PLEASE PLEASE Help me ?

Thanks

A

=nhw(C8 + B8,P8+O8,"09:00","18:00",0)

I think this formula would do the same thing...

=(NETWORKDAYS(Start_Date, End_Date)-1)*("18:00"-"9:00")+MEDIAN(End_Time,"18:00","9:00")-MEDIAN(Start_Time,"18:00","9:00")

If you really want to fix the code, then change this...

If TotalNetHoursWorked > 1440 Then
    nhw = Int(TotalNetHoursWorked / 60) & ":" & TotalNetHoursWorked Mod 60 & ":00"
Else: nhw = 0.000694444 * TotalNetHoursWorked
End If

To this...

nhw = TimeSerial(0, TotalNetHoursWorked, 0)

 

[blossomthe2nd]

Alpha Frog,

Thanks you so very much it works perfectly , really appreciate it thanks

 

[blossomthe2nd]

=(NETWORKDAYS(C6, P6)-1)*("18:00"-"9:00")+MEDIAN(O6,"18:00","9:00")-MEDIAN(B6,"18:00","9:00")

OMG I tested it and it was fine I closed file and went back in and now Im getting the NAME? error , can you help ???

 

[AlphaFrog]

The NETWORKDAYS function is part of the Analysis ToolPak add-in. If you re-opened the file in a different computer, make sure the Analysis ToolPak add-in is installed for that computer's copy of Excel.

1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
3. If necessary, follow the instructions in the setup program.

 

[barry houdini]

You could also replicate NETWORKDAYS like this

=(SUM(INT((WEEKDAY(C6-{2,3,4,5,6})+P6-C6)/7))-1)*("18:00"-"9:00")+MEDIAN(O6,"18:00","9:00")-MEDIAN(B6,"18:00","9:00")

....although that formula is something of a "halfway house" in terms of what time values it allows. It allows O6 and B6 to be any time of day......but C6 and P6 dates can't be at weekends, is that what you need?

 

[blossomthe2nd]

Thanks Lads perfecto

Really appreciate it thanks