Steele's Diff2Dates Function without Holidays & Weekends

[Matt]

I am using the Diff2Dates code found on this webpage:
<http://www.accessmvp.com/djsteele/Diff2Dates.html> to calculate the
difference between two General Dates.

I am trying to find a way for the Diff2Dates function to exclude weekends
and holidays in its calculation of the Difference between two General Dates?

I have a table already created called tbl_Holidays with a HolidayDate Field.

For Example:
A field in my report has the following control source:
=Diff2Dates("dhn",[DateRcvd],[DateDue],True)

Where DateRcvd= 7/1/2008 08:48am and DateDue=7/15/2008 2:00pm

Right now the Diff2Dates function returns 14 days 5 hrs 12 mins, and it
should return 9 days 5 hrs 12 mins (removing 4 days for the weekends and one
day for 7/4/2008).

How should the Diff2Dates code be altered in my module to do this
calculation?

Or if you have a better way to perform this calculation, please advise (The
output must be formatted “XX days XX hrs XX minsâ€
Thanks!

 

[Arvin Meyer [MVP]]

You might find this of use:

http://www.mvps.org/access/datetime/date0012.htm

 

[Matt]

If i understand this function correctly, it only works in Days. I need it to
also calculate hours and minutes.

You might find this of use:

http://www.mvps.org/access/datetime/date0012.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am using the Diff2Dates code found on this webpage:
<http://www.accessmvp.com/djsteele/Diff2Dates.html> to calculate the
difference between two General Dates.

I am trying to find a way for the Diff2Dates function to exclude weekends
and holidays in its calculation of the Difference between two General
Dates?

I have a table already created called tbl_Holidays with a HolidayDate
Field.

For Example:
A field in my report has the following control source:
=Diff2Dates("dhn",[DateRcvd],[DateDue],True)

Where DateRcvd= 7/1/2008 08:48am and DateDue=7/15/2008 2:00pm

Right now the Diff2Dates function returns 14 days 5 hrs 12 mins, and it
should return 9 days 5 hrs 12 mins (removing 4 days for the weekends and
one
day for 7/4/2008).

How should the Diff2Dates code be altered in my module to do this
calculation?

Or if you have a better way to perform this calculation, please advise
(The
output must be formatted "XX days XX hrs XX mins"
Thanks!

 

[Arvin Meyer [MVP]]

I haven't tried it, but if you included hours and minutes, I think it would
calculate them as well.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

If i understand this function correctly, it only works in Days. I need it
to
also calculate hours and minutes.

You might find this of use:

http://www.mvps.org/access/datetime/date0012.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I am using the Diff2Dates code found on this webpage:
<http://www.accessmvp.com/djsteele/Diff2Dates.html> to calculate the
difference between two General Dates.

I am trying to find a way for the Diff2Dates function to exclude
weekends
and holidays in its calculation of the Difference between two General
Dates?

I have a table already created called tbl_Holidays with a HolidayDate
Field.

For Example:
A field in my report has the following control source:
=Diff2Dates("dhn",[DateRcvd],[DateDue],True)

Where DateRcvd= 7/1/2008 08:48am and DateDue=7/15/2008 2:00pm

Right now the Diff2Dates function returns 14 days 5 hrs 12 mins, and it
should return 9 days 5 hrs 12 mins (removing 4 days for the weekends
and
one
day for 7/4/2008).

How should the Diff2Dates code be altered in my module to do this
calculation?

Or if you have a better way to perform this calculation, please advise
(The
output must be formatted "XX days XX hrs XX mins"
Thanks!

 

[Matt]

I think this version is more helpful so it would be easier to update the
holidays in a table vs. coding)

http://www.mvps.org/access/datetime/date0006.htm

Also do you think you could assist me with the additional coding and
formatting in include hours and minutes?

Thanks!
Matt

I haven't tried it, but if you included hours and minutes, I think it would
calculate them as well.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

If i understand this function correctly, it only works in Days. I need it
to
also calculate hours and minutes.

You might find this of use:

http://www.mvps.org/access/datetime/date0012.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I am using the Diff2Dates code found on this webpage:
<http://www.accessmvp.com/djsteele/Diff2Dates.html> to calculate the
difference between two General Dates.

I am trying to find a way for the Diff2Dates function to exclude
weekends
and holidays in its calculation of the Difference between two General
Dates?

I have a table already created called tbl_Holidays with a HolidayDate
Field.

For Example:
A field in my report has the following control source:
=Diff2Dates("dhn",[DateRcvd],[DateDue],True)

Where DateRcvd= 7/1/2008 08:48am and DateDue=7/15/2008 2:00pm

Right now the Diff2Dates function returns 14 days 5 hrs 12 mins, and it
should return 9 days 5 hrs 12 mins (removing 4 days for the weekends
and
one
day for 7/4/2008).

How should the Diff2Dates code be altered in my module to do this
calculation?

Or if you have a better way to perform this calculation, please advise
(The
output must be formatted "XX days XX hrs XX mins"
Thanks!