elapsed time calculation help needed

[Mary Bates]

I have two date/time fields that I need to calculate elapsed time for in a
query. I am using the following module. The function needs to consider that
the time should only consider working hours of M - F 8:00 am - 5:00 pm, no
holdiays and no weekends. I have a table that list all holidays for the year.
What would be the best way to append the module?

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As
Date) As String
'**********************************************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As
String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'**********************************************************************************************

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
hours = Format(interval, "h")
Minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & Minutes & seconds <> "000", ", ", " "))

' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(Minutes & seconds <> "00", ", ", " "))

' Minutes part of the string
str = str & IIf(Minutes = "0", "", _
IIf(Minutes = "1", Minutes & " Minute", Minutes & " Minutes"))
str = str & IIf(Minutes = "0", "", IIf(seconds <> "0", ", ", " "))

' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)

End Function

 

[pietlinden]

Arvin Meyer has code on his website that does this.

www.datastrat.com

You need a table of holidays, though, if you want to skip those.