exclude weekends

[alecgreen]

Hi

I have query that deducts 5 days from todays date, basically Now()-5.

But I have encountered an issue with weekends. I need Now()-5
excluding weekends, but not sure how to do this.

Any ideas please

Alec

 

[vanderghast]

Using Choose sounds fine:

Now() - Choose( DatePart( "w", Now( ) ), 6, 7, 7, 7, 7, 7, 8)


Indeed, DatePar("w", Now( ) ) returns a value from 1 (Sunday) to 7
(Saturday), so, if today is a Sunday, we subtract 6; Monday, 7; ... ;
Saturday, 8 ( or would it be 5 ? ) .


Vanderghast, Access MVP

 

[John W. Vinson]

Hi

I have query that deducts 5 days from todays date, basically Now()-5.

But I have encountered an issue with weekends. I need Now()-5
excluding weekends, but not sure how to do this.

You'll also want to include legal holidays - those holidays observed by your
business - right?

See http://www.mvps.org/access/datetime/date0012.htm for some VBA code which
handles both weekends and holidays (using a Holidays table that you must
maintain).

Note also that Now() is *not* today's date - it's the current date and time
accurate to microseconds, though only displayed to the second. Now()-5 is
10:16 on October 10 - not October 10. Use Date() instead of Now(), and the
DateAdd() function rather than subtracting, if you want to be safe and
accurate!