Calculating Date/Time Fields

[jlo]

In the past, I have used a formula to calculate how many work days has
occurred between date fields. I track days excluding Holidays and Weekends
from code I received years back from this discussion group. Everything works
wonderful.

I have another situation similiar but the user wants to track time as well
as the number of days excluding Holidays and Weekends. Is there a way to
alter this formula CalcWorkDays([DateTimeReceived],[DateTimeAcknowledged]) so
I can get days with hours. I should get 15 min. below. Currently I am
getting 1 day.

For instance:
DateTimeReceived DateTimeAcknowledged
4/4/08 10:00 am 4/4/08 10:15 am

Here is the code:
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd 'All days have been
compared
CalcWorkDays = intTotalDays 'Return the value

End Function

 

[ruralguy via AccessMonster.com]

Here's a link that describes a DateTime field:
http://support.microsoft.com/default.aspx/kb/q130514/

An integer can not contain any decimal value so you are stripping the time
component.

In the past, I have used a formula to calculate how many work days has
occurred between date fields. I track days excluding Holidays and Weekends
from code I received years back from this discussion group. Everything works
wonderful.

I have another situation similiar but the user wants to track time as well
as the number of days excluding Holidays and Weekends. Is there a way to
alter this formula CalcWorkDays([DateTimeReceived],[DateTimeAcknowledged]) so
I can get days with hours. I should get 15 min. below. Currently I am
getting 1 day.

For instance:
DateTimeReceived DateTimeAcknowledged
4/4/08 10:00 am 4/4/08 10:15 am

Here is the code:
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd 'All days have been
compared
CalcWorkDays = intTotalDays 'Return the value

End Function