Date Difference - Weekends & Holidays

[Tcs]

I need to calculate the number of days between two dates. I went searching this group and found
what I needed, in a 2003 & 2005 posting. The 2005 posting mentioned:

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

As I understand it, I'm to create an Array named "adtmDates". I haven't played with arrays before,
and am rather lost. Just how am I supposed to implement this?

I believe I need to add my own code to open my recordset and read thru the entire table, calling the
dates function to calc for each record. But where do I, how do I, include this array for all the
holidays I need to skip? My dates go back to 2000, so I potentially have 6 years worth of holidays
to account for.

Any help is appreciated, thanks in advance,

Tom

 

[Michel Walsh]

Hi,



Array( #1/1/2001#, #2/2/2002#, #3/3/2003#)


is an array (origin 0, of variant) holding the three given dates.


? Array( #1/1/2001#, #2/2/2002#, #3/3/2003#) (2)
2003.03.03



Hoping it may help
Vanderghast, Access MVP

 

[Tcs]

Well...here's what I'm supposed to do:
----------
' In:
' dtmStart:
' Date specifying the start of the range (inclusive)
' dtmEnd:
' Date specifying the end of the range (inclusive)
' (dates will be swapped if out of order)
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value.
' Out:
' Return Value:
' Number of working days (not counting weekends and optionally, holidays)
' in the specified range.
' Example:
' Debug.Print dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, _
' Array(#1/1/2000#, #7/4/2000#))
'
' returns 2, because 7/2/2000 is Sunday, 7/4/2000 is a holiday,
' leaving 7/3 and 7/5 as workdays.
----------

And here's what I've coded:
----------
strStartDate = Chr$(34) & DAOrs![Application Date] & Chr$(34)
strEndDate = Chr$(34) & DAOrs![Application Date] & Chr$(34)

DAOrs![Time To Issue] = dhCountWorkdaysA(strStartDate, strEndDate, _
Array("#1/3/2000#, #1/17/2000#, #5/29/2000#, #7/4/2000#, #9/4/2000#, #11/23/2000#, #11/24/2000#,
#12/25/2000#, & _
#1/1/2001#, #1/15/2001#, #5/28/2001#, #7/4/2001#, #9/3/2001#, #11/22/2001#, #11/23/2001#,
#12/24/2001#, #12/25/2001#, #12/31/2001#, & _
#1/1/2002#, #1/14/2002#, #5/27/2002#, #7/4/2002#, #9/4/2002#, #11/21/2002#, #11/22/2002#,
#12/25/2002#, #12/31/2002#, & _
#1/1/2003#, #1/13/2003#, #5/26/2003#, #7/4/2003#, #9/5/2003#, #11/20/2003#, #11/21/2003#,
#12/25/2003#, #12/31/2003#, & _
#1/1/2004#, #1/19/2004#, #5/28/2004#, #7/5/2004#, #9/3/2004#, #11/18/2004#, #11/19/2004#,
#12/24/2004#, #12/31/2004#, & _
#1/3/2005#, #1/17/2005#, #5/30/2005#, #7/4/2005#, #9/5/2005#, #11/17/2005#, #11/18/2005#,
#12/26/2005#, & _
#1/2/2006#, #1/16/2006#, #5/29/2006#, #7/4/2006#, #9/4/2006#, #11/23/2006#, #11/24/2006#,
#12/25/2006#, #12/31/2006#"))
----------

VBA obviously doesn't like this, as I can't get rid of a compiler error. I originally tried withOUT
the double quotes and ampersands, then with. Without, VBA takes the first '#' character in the line
and moves it to the last position (just left of the underscore) on the previous line. I'm missing
something. Would you happen to know what?

And it also seems that VBA doesn't like my strings wrapped in double quotes. So how *do* I feed the
dates from my records, without hard coding the starting & ending dates? I can't even get a compile
even when I leave the optional aray out of my code.

Thanks,

Tom

 

[Douglas J. Steele]

You need neither the ampersands nor the quotes within the Array statement:

DAOrs![Time To Issue] = dhCountWorkdaysA(strStartDate, strEndDate, _
Array(#1/3/2000#, #1/17/2000#, #5/29/2000#, #7/4/2000#, #9/4/2000#,
#11/23/2000#, _
#11/24/2000#, #12/25/2000#, & _
#1/1/2001#, #1/15/2001#, #5/28/2001#, #7/4/2001#, #9/3/2001#,
#11/22/2001#, _
#11/23/2001#, #12/24/2001#, #12/25/2001#, #12/31/2001#, _
#1/1/2002#, #1/14/2002#, #5/27/2002#, #7/4/2002#, #9/4/2002#,
#11/21/2002#, _
#11/22/2002#, #12/25/2002#, #12/31/2002#, & _
#1/1/2003#, #1/13/2003#, #5/26/2003#, #7/4/2003#, #9/5/2003#,
#11/20/2003#, _
#11/21/2003#, #12/25/2003#, #12/31/2003#, & _
#1/1/2004#, #1/19/2004#, #5/28/2004#, #7/5/2004#, #9/3/2004#,
#11/18/2004#, _
#11/19/2004#, #12/24/2004#, #12/31/2004#, & _
#1/3/2005#, #1/17/2005#, #5/30/2005#, #7/4/2005#, #9/5/2005#,
#11/17/2005#, _
#11/18/2005#, #12/26/2005#, & _
#1/2/2006#, #1/16/2006#, #5/29/2006#, #7/4/2006#, #9/4/2006#,
#11/23/2006#, _
#11/24/2006#, #12/25/2006#, #12/31/2006#))

The _ continuation character requires the & when you're dealing with long
strings because what you're really doing there is concatenating substrings
together. You split

"My first line of text. My second line of text"

into

"My first line of text." & _
"My second line of text"

because that's the equivalent of having

"My first line of text." & "My second line of text"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well...here's what I'm supposed to do:
----------
' In:
' dtmStart:
' Date specifying the start of the range (inclusive)
' dtmEnd:
' Date specifying the end of the range (inclusive)
' (dates will be swapped if out of order)
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value.
' Out:
' Return Value:
' Number of working days (not counting weekends and optionally,
holidays)
' in the specified range.
' Example:
' Debug.Print dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, _
' Array(#1/1/2000#, #7/4/2000#))
'
' returns 2, because 7/2/2000 is Sunday, 7/4/2000 is a holiday,
' leaving 7/3 and 7/5 as workdays.
----------

And here's what I've coded:
----------
strStartDate = Chr$(34) & DAOrs![Application Date] & Chr$(34)
strEndDate = Chr$(34) & DAOrs![Application Date] & Chr$(34)

DAOrs![Time To Issue] = dhCountWorkdaysA(strStartDate, strEndDate, _
Array("#1/3/2000#, #1/17/2000#, #5/29/2000#, #7/4/2000#, #9/4/2000#,
#11/23/2000#, #11/24/2000#,
#12/25/2000#, & _
#1/1/2001#, #1/15/2001#, #5/28/2001#, #7/4/2001#, #9/3/2001#,
#11/22/2001#, #11/23/2001#,
#12/24/2001#, #12/25/2001#, #12/31/2001#, & _
#1/1/2002#, #1/14/2002#, #5/27/2002#, #7/4/2002#, #9/4/2002#,
#11/21/2002#, #11/22/2002#,
#12/25/2002#, #12/31/2002#, & _
#1/1/2003#, #1/13/2003#, #5/26/2003#, #7/4/2003#, #9/5/2003#,
#11/20/2003#, #11/21/2003#,
#12/25/2003#, #12/31/2003#, & _
#1/1/2004#, #1/19/2004#, #5/28/2004#, #7/5/2004#, #9/3/2004#,
#11/18/2004#, #11/19/2004#,
#12/24/2004#, #12/31/2004#, & _
#1/3/2005#, #1/17/2005#, #5/30/2005#, #7/4/2005#, #9/5/2005#,
#11/17/2005#, #11/18/2005#,
#12/26/2005#, & _
#1/2/2006#, #1/16/2006#, #5/29/2006#, #7/4/2006#, #9/4/2006#,
#11/23/2006#, #11/24/2006#,
#12/25/2006#, #12/31/2006#"))
----------

VBA obviously doesn't like this, as I can't get rid of a compiler error.
I originally tried withOUT
the double quotes and ampersands, then with. Without, VBA takes the first
'#' character in the line
and moves it to the last position (just left of the underscore) on the
previous line. I'm missing
something. Would you happen to know what?

And it also seems that VBA doesn't like my strings wrapped in double
quotes. So how *do* I feed the
dates from my records, without hard coding the starting & ending dates? I
can't even get a compile
even when I leave the optional aray out of my code.

Thanks,

Tom

Hi,



Array( #1/1/2001#, #2/2/2002#, #3/3/2003#)


is an array (origin 0, of variant) holding the three given dates.


? Array( #1/1/2001#, #2/2/2002#, #3/3/2003#) (2)
2003.03.03



Hoping it may help
Vanderghast, Access MVP

 

[Tcs]

Thank you sir. I started out withOUT quotes, but VB never liked whatever I tried. Appparently I
never tried dropping the ampersands altogether. (Too conditioned to always using the darn things, I
guess.)

I made the changes you suggested, and it works like a charm! I do appreciate the assistance.

Thanks a bunch,

Tom