Building a Difficult DateDiff Expression

[jma108]

Hello all, this is my first post and I am excited to start sharing information. As my first, rather complicated question, I've decided to ask for help in building a very difficult (for me) DateDiff Expression in a simple Select Query.

I need to find the number of hours between two dates ([COMPLETED] and [OPENDATE]). The kicker, however, comes with only wanting to measure work days (8 hour days, excluding weekends, holidays are ok). Is this even possible? Currently, I am using the following (weekends are currently included):

Hrs: (DateDiff("h",CDate([OPENDATE]),CDate([COMPLETED])))+(Format(CDate([COMPLETED]-[OPENDATE]),"hh"))+((Format(CDate([COMPLETED]-[OPENDATE]),"nn")/60))

Any help is appreciated!

 

[DCrake]

Right you need to break down the data into different elements

First you need to know when a day starts
Second when a day ends

What is your start time
What is your end time

You will have three bits of info to add together

1. difference in hours between the start hour and the end of day hour
2. difference in hours between the start of day hour and theend hour
3. Number of working days between the two dates EXCLUDING the start and finish dates (already established in 1 & 2) times 8 (hours in a day)

by adding 1, 2 & 3 together will give you the correct answer.

Thats the logic. Next you need to create a function to be able to process it. This function will need the following items:

Start Date
Start Time
End Date
End time

From this you will be able to generate the answer as per the above.

David

 

[allan57]

Found this function on the net a long time ago, dont know who's code it was originally but it might be worth giving it try (I've not had an occassion to use it yet)

Public Function WorkingHours(datBegin As Date, datEnd As Date) As Double
    ' Beginning and end of the day
    Const tmecDayStart As Date = "7:30:00"
    Const tmecDayEnd As Date = "18:00:00"
    
    Dim datTotalTime As Date      ' Running total of time worked
    datTotalTime = 0                ' Initialized at 0
    
    Dim datTest As Date             ' Variable to keep the date where calculating
    datTest = datBegin              ' Initialized as the beginning date

    ' If the first day begins before 7:30, make it 7:30
    If TimeValue(datTest) < tmecDayStart Then
        datTest = DateValue(datTest) + TimeValue(tmecDayStart)
    ' If the first day begins after 18:00, make it 7:30 tomorow
    ElseIf TimeValue(datTest) > tmecDayEnd Then
        datTest = DateValue(datTest + 1) + TimeValue(tmecDayStart)
    Else
        ' If the first day starts after 7:30 and before 18:00, make it 7:30
        '  and substract the difference to the running total
        datTotalTime = datTotalTime - (TimeValue(datTest) - TimeValue(tmecDayStart))
        datTest = DateValue(datTest) + TimeValue(tmecDayStart)
    End If
    
    ' For every date
    Do While DateValue(datTest) < DateValue(datEnd)
        ' Skip weekends
        If Weekday(datTest, vbMonday) <= 5 Then
            ' Adds a complete day
            datTotalTime = datTotalTime + (TimeValue(tmecDayEnd) - TimeValue(tmecDayStart))
        End If
        
        ' Iterate (adds a day)
        datTest = datTest + 1
    Loop
    
    ' Remove the time for the last incomplete day
    ' Skip the weekend
    If Weekday(datTest, vbMonday) <= 5 Then
        ' If the last day ends before 7:30, add nothing
        If TimeValue(datEnd) < tmecDayStart Then
            ' Do nothing
        ' If the last day ends after 18:00, add a day
        ElseIf TimeValue(datEnd) > tmecDayEnd Then
            ' Adds a complete day
            datTotalTime = datTotalTime + (TimeValue(tmecDayEnd) - TimeValue(tmecDayStart))
        ' The last day ends between 7:30 and 18:00, add the time worked
        Else
            datTotalTime = datTotalTime + (TimeValue(datEnd) - TimeValue(tmecDayStart))
        End If
    End If
    
    ' Returns the number of minutes divided by 60, so it gives the number of hours
    ' I didn't returned DateDiff("h", CDate(0), datTotalTime) because Datediff returns a long.
    WorkingHours = DateDiff("n", CDate(0), datTotalTime) / 60
End Function

 

[jma108]

Thanks to both of you! I used allan57's function and after altering the start/end times and adjusting for an inherent data flaw in my database, I now have exactly what I was looking for.

Thank you so much!