how do i work out the number of working hours between 2 dates+times

[jamiec1]

this is the bain of my life at the moment and its driving me nuts. I found a formula to do it in xl but i cant get it in access. Its for a helpdesk project im working on so i need to know how many working hours a job has been open for. Any help would be really appreciated

 

[gwinn7]

You can use DateDiff function. This returns the difference between two dates and times. You will have to specify a format (ie. hours). I recommend you lookup the function in the Access Help.

Gary
gwinn7

 

[binky]

Maybe this will help...


Dim dtDate1 As Variant
Dim dtDate2 As Variant
Dim dtDiff As Variant

dtDiff = DateDiff("h", dtDate1, dtDate2)

This will give you the number of hours between two date/time values. Do some validation on the two original values and you should be able to figure out what you need. If you need more help let me know and I will do what I can!

HTH
Ken

 

[gwinn7]

Ummm. Stay away from Variants!

Gary
gwinn7

 

[MichaelRed]

The following is an approach which has been implemented. I would generally NOT embed the holidays within the code, but have done so here to provide the entire soloution within a single object. A much better approach tot he holidays wouls be to place them in a table, and to provide a maintenance facility to update them as necessary. I would also recommend that a check be made on the word dates vs. Holiday dates, to insure that the holiday dates include the entire range of work dates.

Additional enhancements which would be useful/necessary in a business / production environment would be to include variations in the working hours.

Public Function basWrkHrs(StDate As Date, EndDate As Date) As Double

    'Get the number of work HOURS between the given dates

    Dim blnHoliFnd As Boolean       'Flag for Hloiday found
    Dim Holidate(21) As Date        'Table of Holidays
    Dim Idx As Long                 'Index for start/end dates
    Dim Kdx As Long                 'Index / counter for Number of days
    Dim Jdx As Integer              'Index doe the Hloidate array
    Dim MyDate As Date              'Tempdate
    Dim AccumTime As Double         'Hours Accumulated

    Const MinsPerDay = 1440         'Every Minute of the DAY!!
    Const MinsPerHr = 60#           '60 Minutes per Hour

    'For MAINTENANCE purposes, the array should be in a TABLE
    'There SHOULD be a form to add/edit/delete the table.
    
    'At run time, the TABLE should be wholy loaded into the ARRAY
    'to promote execution effiency.

    'Array(Table) of Holiday Dates
    Holidate(0) = #1/1/2001#        'NewYearsDay
    Holidate(1) = #1/17/2001#       'Martin Luther King Day
    Holidate(2) = #2/2/2001#        'Groundhog Day
    Holidate(3) = #2/12/2001#       'Lincon's Birthday
    Holidate(4) = #2/14/2001#       'Valentine's Day
    Holidate(5) = #2/21/2001#       'President's Day
    Holidate(6) = #2/22/2001#       'Washington's Birthday
    Holidate(7) = #3/8/2001#        'Ash Wednesday
    Holidate(8) = #3/17/2001#       'St. Patrick's Day
    Holidate(8) = #4/1/2001#        'April Fool's Day
    Holidate(9) = #4/20/2001#       'Passover
    Holidate(10) = #4/21/2001#      'Good Friday
    Holidate(11) = #5/5/2001#       'Cinco de Mayo
    Holidate(12) = #5/14/2001#      'Mother's Day
    Holidate(13) = #6/11/2001#      'Pentecost
    Holidate(14) = #6/18/2001#      'Father's Day
    Holidate(15) = #7/4/2001#       'Independence Day
    Holidate(16) = #9/4/2001#       'Labor Day
    Holidate(17) = #10/31/2001#     'Halloween
    Holidate(18) = #11/11/2001#     'Vetran's Day
    Holidate(19) = #11/23/2001#     'Thanksgiving
    Holidate(20) = #12/25/2001#     'Christmas
    Holidate(21) = #12/31/2001#     'New Year's Eve


    'Get the incremental Minutes for the Start & End Dates
    If (Not (Weekday(StDate) = vbSaturday Or Weekday(StDate) = vbSunday)) Then
        AccumTime = DateDiff("n", StDate, Format(StDate + 1, "mm/dd/yy"))
    End If

    If (Not (Weekday(EndDate) = vbSaturday Or Weekday(EndDate) = Sunday)) Then
        AccumTime = AccumTime + DateDiff("n", Format(EndDate, "mm/dd/yy"), EndDate)
    End If

    MyDate = Format(StDate + 1, "Short Date")

    'Loop for each day INSIDE the interval
    For Idx = CLng(StDate + 1) To CLng(EndDate) - 1

        blnHoliFnd = False

        If (Weekday(MyDate) = vbSaturday Or Weekday(MyDate) = vbSunday) Then
            blnHoliFnd = True
            GoTo NoTime
        End If

        For Jdx = 0 To UBound(Holidate)

            If (Holidate(Jdx) = MyDate) Then
                blnHoliFnd = True
                Exit For
'             Else
'                Do Nothing, it is NOT a Workday
            End If

        Next Jdx

NoTime:

        'count WHOLE (Work) days
        If (blnHoliFnd = False) Then
            Kdx = Kdx + 1
        End If
    
        MyDate = DateAdd("d", 1, MyDate)
        

    Next Idx

    'Got the number of days.  Now, add work minutes to acuumtime
    AccumTime = AccumTime + CSng(Kdx) * CSng(MinsPerDay)

    basWrkHrs = AccumTime / MinsPerHr

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jamiec1]

That is amazing ..
Thanks for the help it is really appreciated.

jamie

 

[Lavey]

Michael,

I am using a function very similar to this to drop non working days and give the hours difference between two dates, one question; the results from my function are used in a query - I then want to build a crosstab based on the values produced by the function, but they are treated as 'text' and therefore I cannot 'sum' or 'avg' on these calculated fields...
I have used convert to double (cdbl)(as I want .0 in the result), but still no avail - I have tried to return the result of the function as double also, but still cannot change the result from text to a number

any ideas .....
Thx

 

[Sameal]

Michael,

What font are you using to display your code? I want to read it but it just shows up as lines for making text boxes. Miss out on a lot of your posts because of this.

 

[MichaelRed]

Lavey,

I have no idea why your procedure does ... After all, I can't see it. Mine does appear to return a double and be ammenable to use in aggregate functions.

Sameal, I (pseudo Sgt. Schultz) also know nothing about this. I do not choose/select a font for my posts, so it is just the default. It "loks like" courier to me, but I'm not big on fonts, so I could EASILY be wrong.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Lavey]

Michael,

I have attached my code below, I have even converted to double just before i return the result but still no joy...
I am I missing something vital here ?

Any input you have wuold be most helpful!

Public Function NonWorkingDays(ByVal DateIn As Date, ByVal DateOut As Date)
On Error GoTo CalcError:

Dim TotalHours As Long
Dim HoursToDrop As Long
Dim StartDate As Date
Dim EndDate As Date

'set up start/end dates/totalhours and reset hourstodrop to 0
StartDate = Format(DateIn, "general date&quot
EndDate = Format(DateOut, "general date&quot
TotalHours = DateDiff("n", StartDate, EndDate)
HoursToDrop = 0

Dim WeekStart As Byte
Dim WeekEnd As Byte

WeekStart = WeekDay(StartDate)
WeekEnd = WeekDay(EndDate)

Dim Monday As Date
Dim NewString As String

'if received and completed on sat or sun then calc hours only or set first working day to monday morning
Select Case WeekStart
Case 1
If WeekEnd = 1 And TotalHours < 1440 Then
NonWorkingDays = Format(TotalHours / 60, &quot;0.0&quot
Exit Function
Else
NewString = StartDate + 1
NewString = Left(NewString, 8) & &quot; 08:00&quot;
Monday = NewString
HoursToDrop = DateDiff(&quot;n&quot;, StartDate, Monday)
End If
Case 7
If WeekEnd = 7 Or WeekEnd = 1 And TotalHours < 2880 Then
NonWorkingDays = Format(TotalHours / 60, &quot;0.0&quot
Exit Function
Else
NewString = StartDate + 2
NewString = Left(NewString, 8) & &quot; 08:00&quot;
Monday = NewString
HoursToDrop = DateDiff(&quot;n&quot;, StartDate, Monday)
End If
End Select


'add up all sat/sun between startdate and end date
Dim DateCounter As Date

Select Case NewString
Case &quot;&quot;
DateCounter = StartDate
Do
If WeekDay(DateCounter) = 7 Or WeekDay(DateCounter) = 1 Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop While DateCounter < EndDate
Case Else
DateCounter = NewString
Do
If WeekDay(DateCounter) = 7 Or WeekDay(DateCounter) = 1 Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop While DateCounter < EndDate
End Select


'Too DAMN SLOOOOOOOOOOW with this piece of code!! was a dcount on holiday table
'Dim HoliDays As Integer
'
' HoliDays = DCount(&quot;holidate&quot;, &quot;holidaytable&quot;, &quot;(holidate Between #&quot; & Format(StartDate, &quot;short date&quot & &quot;# And #&quot; & Format(EndDate, &quot;short date&quot & &quot;#)&quot
' If Not HoliDays = 0 Then
' HoliDays = HoliDays * 1440
' HoursToDrop = HoursToDrop + HoliDays
' End If


'instead use an array created on app start
Dim J As Date
Dim I As Byte
Dim Holidays As Long

StartDate = Format(StartDate, &quot;short date&quot
EndDate = Format(EndDate, &quot;short date&quot

For J = StartDate To EndDate
For I = 0 To UBound(HoliArray) - 1
If Not WeekDay(J) = 1 Or WeekDay(J) = 7 Then
If HoliArray(I) = J Then
Holidays = Holidays + 1
End If
End If
Next I
Next J
If Holidays <> 0 Then
Holidays = Holidays * 1440
HoursToDrop = HoursToDrop + Holidays
End If

'true working hours is total hours minus the hours to be dropped - and there you go !

NonWorkingDays = TotalHours - HoursToDrop
If NonWorkingDays < 0 Then NonWorkingDays = NonWorkingDays + Holidays
NonWorkingDays = CDbl(Format(NonWorkingDays / 60, &quot;0.0&quot)
Exit Function

CalcError:
If Err.number = 94 Or Err.number = 13 Then
NonWorkingDays = Null
Else
DisplayMessage &quot;Please report 'TR calc error - &quot; & Err.number & &quot; &quot; & Err.Description & &quot; '&quot;
End If
End Function


Thx alot!!

 

[jamiec1]

Michael

The basWrkHrs function returns 25.5 as a response to

SELECT basWrkHrs(#11/20/2001 15:0:00#,#11/21/2001 16:30:00#) AS test;

but working hours in the day would be 9 to 5 so the total number of working hours would be 9.5. Is there any way to adapt the code to take this into account.
Its for a helpdesk project so i need to know how many working hours a job has been open for so i can update the priority of a job automatically.

Thanks again for your help

 

[jamiec1]

bump

 

[MichaelRed]

jamiec1.

There are numerous approaches to the overall issue. A part of the soloution is to just subtract the 17 hours per day NOT worked from the Const MinsPerDay = 1440 , the 1440 represents 24 (Hours) * 60 (Minutes), so just change the number of hours to YOUR workday * 60) for the minutes per day. The Start Day and End Day calculations are somewhat more contrived, as you need to calculate the difference between the Start date/time and the end of the working day for the first day's time and the difference between the start of the working day and the item completion date / time for the End Date.

I will show a SAMPLE of calculating the minutes in the Start day:

StDate = #11/05/2001 12:00 PM#
? DateDiff("n", StDate, dateadd("h", 17, Format(StDate, "mm/dd/yy")))
 300

review this calculation. The dateadd(&quot;h&quot;, 17, ... adjusts the time (of te end of the day) to 5 PM. Note that the remainder of the calc remains unchanged. The calc for the last day needs to be modified to reflect your starting time -in an analagous manner.

I feel compeled to point out that this calculation is more 'fine grained' than is generally reasonable for the data available, You are generating 'information' essientially down to the minute, assuming that the work day is STRICTLY constant. This is simply NOT true. Breaks and interruptions occur continuously throughout the workday. Many of the interruptions are NOT generated by the work process, or they may be occassioned by a customer, so the calculation of 'how long it takes' for the task/item is always going to be in error - on the high side- wheather this error includes the 'dead tme' of non-working hours or not is really insignificant.
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++

lavey,

I hardly know where to start. And perhaps I shouldn't. I DID glance at your code. I WILL NOT review it. The FIRST and most obvious issue is that you DO NOT declare the function return value type. This is douoble compounded by setting the function value to DIFFERENT data types, depending on where the value is &quot;set&quot;. I noted three such places in a 15 second 'glance'.

The only other advice I can offer is not re 'your' code, but to either hire a 'real' programmer, or to take procedures from places like Tek-Tips which DO WORK, and modify them MINIMALLY to your needs.

I do not mean to be 'harsh', but I need to use my time more in the line of obtaining gainful employment. I do offer some help on Tek-Tips, but this is limited to efforts where I can do so quickly, such as posting routines which I already have (or can modify in just a few minutes), or to just point out an item for others to consider.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Lavey]

Michael,

Thx for your input, no offence taken! Any ideas are a route to investigate! I've only been writing code for about 6 months (I guess it shows!), I have written a quick adhoc to convert to double and it works - now I'll take a look at the bulk of this code and start over

I'm sure with the knowlegde you have it's not gonna take you long to get employemt (gainful !), your posts here are more than appreciated by us 'newbies'.

Thx!!

 

[jamiec1]

thanks ..
i will give that a go