Time Diff Module

[Pete666]

I found this module some time ago and has been used in most of the databases I've created since. This will calculate the time diff (currently set to minutes) between 2 dates, it also removes the time between 5:00PM and 9:00AM so only calculates the time during the working day. With the use of a table called Holidays you can also remove any public holidays.


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

 

[mr moe]

Undefined function

guys please, can someone help? I'm using this function and it's not working, I put it in a query, but I keep getting a message undefined function !!!!!!

 

[boblarson]

guys please, can someone help? I'm using this function and it's not working, I put it in a query, but I keep getting a message undefined function !!!!!!

You need to put the function in a STANDARD module, not a form module. Is that where you put the code that was there? Did you paste the whole thing in, including the Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Single part?

 

[mr moe]

The way i did it is by creating a new module and then pasting the whole thing in it. Is that right?

 

[mr moe]

I just fiqured that out, this is so so wired, the problem is that I had the module name in the module name properties same as the module real name! when I changed the module name in teh module properties to a different one it worked!!! can someone please explain, because I'm pulling my hair out and soon i'm going to be bold.

 

[boblarson]

You can't have two objects named the same, so it didn't know which to use. You can't have the module which holds the function named the same as a function or sub. That is why.