Date difference - working hours only (1 Viewer)

SteveClarkson · Oct 21, 2008

Hello all - long time no see! (lucky for all of you!)

I have a working db system that I use a call logging system for an IT helpdesk, which shows the average call resolution time (management just LOVE statistics!).

Anyway - at the moment, I am just using datediff functions, which doesn't make us look very good, if we pick up a call at 4:55pm, and fix it (effectively 10 minutes later) at 9:05am the next morning. The datediff function is picking up all the hours in between, which isn't super.

Anyway - I decided to get this sorted, by knicking other peoples code ;-)

I have the code below, pilfered from this website, but can't make it work in cases where the date changes.
For example, a call logged at 10am, and closed at 11am works fine, it shows 60 minutes.
However, if those same hours are used, but on different days, I get very odd results.

Can anyone spot anything obvious. If it would help, I can post an example of all the code working (or not, as the case may be).

Thanks!

The function I have is:

Code:

Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Single

Dim intGrossDays As Integer
Dim intGrossHours As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayhours As Single
Dim EndDayhours As Single

NetWorkhours = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day

WorkDayStart = DateValue(dteEnd) + TimeValue("09:00am")
WorkDayend = DateValue(dteStart) + TimeValue("05:00pm")
StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours

If StartDayhours < 0 Then
StartDayhours = 0
End If
If EndDayhours > 8 Then
EndDayhours = 8
End If

'Calculate total hours and days between start and end times

intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossHours = DateDiff("n", (dteStart), (dteEnd))

'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
nonWorkDays = nonWorkDays + 1
End If
End If
Next i
'Calculate number of work hours

Select Case intGrossDays
Case 0
'start and end time on same day
NetWorkhours = intGrossHours
Case 1
'start and end time on consecutive days
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours
Case Is > 1
'start and end time on non consecutive days
NetWorkhours = NetWorkhours - (nonWorkDays * 1)
NetWorkhours = (intGrossDays - 1 - nonWorkDays) * 8
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours

End Select
End Function

nIGHTmAYOR · Oct 22, 2008

let me just hint you by an example how things work in vb regarding time , supposingly :
call started #1/1/2008 11:59:00 AM#
call ended #1/1/2008 12:04:00 PM#
then a simple function such as :

Code:

MsgBox Minute(#1/1/2008 12:04:00 PM# - #1/1/2008 11:59:00 AM#)

would return the integer 5
also

Code:

MsgBox Format(#1/1/2008 12:04:00 PM# - #1/1/2008 11:59:00 AM# , "hh:mm:ss")

would return the string "00:05:00"

so you see time is very flexible to manipulate just like numbers , now formating the outcome is the triky part , yet there is nothing FORMAT() , DAY() , HOUR() , MINUTE() , SECOND() functions cant solve.

DCrake · Oct 22, 2008

First thing make sure the format is dd/mm/yyyy

second noticed that

StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)

The two variables are called hours but the datediff is responding with minutes.

intGrossHours = DateDiff("n", (dteStart), (dteEnd))

Same again further down

DCrake · Oct 22, 2008

Had time to test this function and have modifed it to work correctly

Code:

Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Single

Dim intGrossDays As Integer
Dim intGrossMins As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayMins As Single
Dim EndDayMins As Single
Dim NetworkMins As Integer
NetworkMins = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day

WorkDayStart = DateValue(dteEnd) + TimeValue("09:00am")
WorkDayend = DateValue(dteStart) + TimeValue("05:00pm")
StartDayMins = DateDiff("n", dteStart, WorkDayend)
EndDayMins = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours


'Calculate total hours and days between start and end times

intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossMins = DateDiff("n", (dteStart), (dteEnd))

'count number of weekend days and holidays (from a table called "Holidays" that lists them)

For i = 0 To intGrossDays
    dteCurrDate = dteStart + i
    If Weekday(dteCurrDate, vbSaturday) < 3 Then
        nonWorkDays = nonWorkDays + 1
    Else
        'If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
        'nonWorkDays = nonWorkDays + 1
        'End If
    End If
Next i
'Calculate number of work hours

Select Case intGrossDays
    Case 0
        'start and end time on same day
        NetWorkhours = (intGrossMins - ((nonWorkDays) * 1440))
    Case 1
        'start and end time on consecutive days
        NetWorkhours = StartDayMins + EndDayMins
    Case Is > 1
        'start and end time on non consecutive days
        NetWorkhours = NetWorkhours - (nonWorkDays * 1)
        NetWorkhours = (intGrossDays - 1 - nonWorkDays) * 8
        NetWorkhours = NetWorkhours + StartDayMins
        NetWorkhours = NetWorkhours + EndDayMins
    
    End Select
End Function

Have tested all variations

CodeMaster:

SteveClarkson · Oct 23, 2008

DCrake,

WOW! Thank you so much for your help! Sorry for my delay replying, had a day off sick, and have been busy catching up with myself!

I am afraid it doesn't seem to be working for me?

I have attached a sample of how I am using it - perhaps I am being extra thick (very likely!)

If you run the form in the DB, that will give you a couple of boxes for date from/to, and the result from the function you fixed.

As before, if you enter dates:
20/10/08 4pm TO 21/10/08 10am
It works fine, and returns 120 minutes as I would expect

However, if I enter dates:
20/10/08 4pm TO 22/10/08 10am
Rather than returning 600 minutes as I would expect (1x 8 hour day, and 1 hour either end of each day, from 4-5 and 9-10), it instead returns 128!!!

Continuing to increase the number of days it spans, the results just become more and more confusing!

As I say, you help is VERY much appreciated, thank you so much in advance (not that I am sucking up, at ALL!)

DCrake · Oct 24, 2008

I have made the necessary changes so try it now. It seemed it was the way it was reading the times, anyway give it a go.

David

SteveClarkson · Oct 24, 2008

Sorry - am I being simple?

Where have you made changes?

DCrake · Oct 24, 2008

The zip file did not update itself I will post a new version for you shortly

DCrake · Oct 24, 2008

Here is the revised version

SteveClarkson · Oct 24, 2008

Absolutely bloomin' wonderful!

Thank you so very very much!

TrishB · Nov 12, 2008

Wow, this is exactly what I am looking for. I'm not very good, but how would I go about adapting this to use in a query where it evaluates the start date and end date fields for a recordset?

DCrake · Nov 13, 2008

If your query returns the two entities as columns then add a further column to your query that calls the funtion and pass the two parameters. If you want send me a copy of the query I will have a look at it.

David

majid.pervaiz · Jun 20, 2016

Dear DCrake, you solution was excellent but I want to ask one question. the text boxes you have in database3 were unbound which cannot store the date/time in database.
appreciate if you can support with this

Archana92 · Nov 22, 2017

Hi Everyone,

I'm new to Access database,
Need your help to calculate the Working hours (Monday 7:00 AM till Saturday 7:00 AM) from the given two Start and end date and time. We can include holidays in working hours.

For Example: Input: Giving with Start date as "17/11/2017 07:00"-Friday
End date as "20/11/2017 07:00"-Monday
Output: Working hours should be 24 hours.
Also we have a TAT_slab like "24,36,48,72 and 96" hours.
Need to calculate the time accordingly based on the TAT slab.

Please help to get the output in Access.

arnelgp · Nov 22, 2017

what is your calcuation for TAT slab for each 5 tat slab there.
what are your dayoffs?

Grumm · Nov 22, 2017

Maybe you can use the code mentionned in
https://access-programmers.co.uk/forums/showpost.php?p=1516621&postcount=16
or
https://access-programmers.co.uk/forums/showpost.php?p=1464028&postcount=41

The first one just calculates the time between 2 dates.
The second topic calculates the time between 2 working dates. In the code you can change some variables to tell what the start and stop time is. And what the no working days are (like it is in the post it is saterday and sunday.)
The output is formated but you can change that too.

Archana92 · Nov 23, 2017

Sure, I will share the details.

1. There are around 5 different hours TAT slab like 24, 36, 48, 72 and 96 which is the deadline to complete the allotted task (TAT Slab).
TAT slab will be based on the given input from user end. They can choose any TAT slab from the above.
Once they input the TAT slab,Received date and time, and completion time.
Based on the TAT slab, say if they enters 24 hours (Duration for completing the task)TAT Slab. Will need to get the Output of working hours which will count only from (Monday Morning 07:01 till Saturday Morning 06:59)
Working hours calculation will differ based on the given input TAT Slab.

2. Dayoffs will be from Saturday morning 7:00 till Monday morning 7:00(to be excluded from time calculation)

Your help will be much appreciated.
Thank you.

Date difference - working hours only (1 Viewer)

SteveClarkson

Registered User.

nIGHTmAYOR

Registered User.

DCrake

Remembered

DCrake

Remembered

SteveClarkson

Registered User.

Attachments

DCrake

Remembered

SteveClarkson

Registered User.

DCrake

Remembered

DCrake

Remembered

Attachments

SteveClarkson

Registered User.

TrishB

New member

DCrake

Remembered

majid.pervaiz

Registered User.

Archana92

New member

arnelgp

..forever waiting... waiting for jellybean!

Grumm

Registered User.

Archana92

New member

Similar threads

Users who are viewing this thread