MS Access Response Time Calculation Help

[qcjustin]

Hi All,

New to the forums, glad to be here.
I am having an issue with a response time calculation module in access.
I basically need it to calculate working hours and minutes between two date/time fields.
I have a module for it, but for some reason for every whole 9 hour day that clocks, it is losing 1 hour of calculation.
So after 1 full 9 hour day past, it is 1 hour off, when 2 full days have past, it is 2 hours short, etc.

I have included a sample of the coding. Can someone take a look and see if you notice anything off?
This has constants on the script for working hours, which are 0800-1700.

Here is an example of two dates and the calculation/expected calculation.

Any help is much appreciated. ResponseCalc.accdb

StartDate	EndDate	Output	TrueResponse
11/5/2012 11:54	11/9/2012 15:45	36.85	39.85

Public Function NetWorkhours(dteStart As Date, dteEnd As Date, Spellout As Boolean) As Variant


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("08:00:00")
WorkDayend = DateValue(dteStart) + TimeValue("17:00:00")
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
NetworkMins = (intGrossMins - ((nonWorkDays) * 1440))
Case 1
'start and end time on consecutive days
NetworkMins = StartDayMins + EndDayMins
Case Is > 1
'start and end time on non consecutive days
NetworkMins = (((intGrossDays) - nonWorkDays - 1) * 480) + (StartDayMins + EndDayMins)

End Select

If Spellout = True Then
NetWorkhours = MinsToTime(NetworkMins) ' hours and mins
Else
NetWorkhours = NetworkMins ' minutes only
End If




End Function




Function MinsToTime(Mins As Integer) As String
MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")
End Function

[qcjustin]

Ok, I got it figured out. My work days are 9 hours, and for formula had the number of minutes for 8 hours(480 vs 540)
The fixed code snipped is below.
Now I am having another problem.
It seems the time is being calculated for the start date before the work day starts.
The work days starts @ 8am, but the code is still counting if the start date is before 8am.
The very 1st section after declarations is supposed to handle this, but is not working.

Public Function NetWorkhours(dteStart As Date, dteEnd As Date, Spellout As Boolean) As Variant


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("08:00:00")
WorkDayend = DateValue(dteStart) + TimeValue("17:00:00")
StartDayMins = DateDiff("n", dteStart, WorkDayend)
EndDayMins = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours


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




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
NetworkMins = (intGrossMins - ((nonWorkDays) * 1440))
Case 1
'start and end time on consecutive days
NetworkMins = StartDayMins + EndDayMins
Case Is > 1
'start and end time on non consecutive days
NetworkMins = (((intGrossDays - 1) - nonWorkDays) * 540) + (StartDayMins + EndDayMins)

End Select

If Spellout = True Then
NetWorkhours = MinsToTime(NetworkMins) ' hours and mins
Else
NetWorkhours = NetworkMins ' minutes only
End If




End Function




Function MinsToTime(Mins As Integer) As String
MinsToTime = Mins \ 60 & " hour" & IIf(Mins \ 60 <> 1, "s ", " ") & Mins Mod 60 & " minute" & IIf(Mins Mod 60 <> 1, "s", "")
End Function

[Rod]

I have a module for it, but for some reason for every whole 9 hour day that clocks, it is losing 1 hour of calculation.
So after 1 full 9 hour day past, it is 1 hour off, when 2 full days have past, it is 2 hours short, etc.

I have included a sample of the coding. Can someone take a look and see if you notice anything off?
This has constants on the script for working hours, which are 0800-1700.

Er ... , 0800 to 1700 is a nine hour day! Your code uses 480 minutes for a working day which is an eight hour day. If you are expecting a nine hour day then change the 480 to 540.


P.S. You got there before me!

[qcjustin]

Thanks Rod. Any ideas on the next question?
I noticed there is something wrong in the end of work day and start of work day logic.
It seems to be on both ends.
When the start time is before work hours, it still counts.
When start time is after 17:00, it subtracts that from the next days total.
So for instance, 12/7/2012 18:33 to 12/10/2012 15:00 Should be 7 Hours(only counting 0800-1500) final day, since 1st is after start time.
This calculates right now at 5.45 hours. Its like its subtracting overage time from the day before.
When the start time is before 0800, it still counts for that days hours, though it should not.

Any thoughts?

[Rod]

I think you are on the right lines; for a span of n days inclusive, day 1 and day n have to be treated as exceptions. When calculating the increment for day 1 there are three conditions:

1. Before 08:00 am = full 9 hour day
2. Between 08:00 am and 05:00 pm - calculate remaining time to 05:00 pm
3. After 05:00 pm = zero

For day n, it is the mirror image:

1. Before 08:00 am = zero
2. Between 08:00 am and 05:00 pm - calculate the time since 08:00 am
3. After 05:00 pm = full 9 hour day

Select Case constructs are probably the most useful here.

Now, something else, I think you should adjust the start and end dates for weekends and holidays. So if the start date is a Saturday, reset it to 08:00 am the following Monday. Similarly if the end date is a Saturday or Sunday, reset it to 05:00 pm the previous Friday. Whether you compensate here for holidays or do it later, I leave to you.

[qcjustin]

Is there anyway you can update the coding or give me a little help editing it? I am at the end of the line for my access coding knowledge, and modifying it as you described will not be easy.

[Rod]

Yes, but don't hold your breath. I'll write something as a module and tell you how to incorporate it in your project.

[qcjustin]

Ok thank you much. I had a sample DB uploaded here also with a module already built if that helps. I will check back

[Rod]

I suggest you test this thoroughly. I got the same result of 39.85 hours which is encouraging.

1. Unzip the following file to a known directory. It will be called WorkDays.bas
2. Go to the VBA coding window.
3. Left click in the upper left-hand pane and select Import File ...
4. Search for the newly unzipped WorkDays.bas, select it and click Open.

You're done!

WorkDays.zip

There are four public functions

1. NetWorkMinutes - accepts two dates and returns the number of work minutes. Read my comments.
2. DateSQl - returns a date string for SQL comparisons.
3. IsHoliday - looks up to see if supplied date is a holiday - returns true or false
4. MinsToTime - your function, unaltered.

I think you can work out how to call these functions. If you can't then shout.

P.S. Left click should of course read, right click - but you seem to have worked that out.

[qcjustin]

Thanks a mil Rod, this worked great!
I have implemented and tested it.
I saw some of your changes and additions, and it makes sense. Just hard to do from scratch with basic coding knowledge.