How do I determine the last business day of the month?

[Paul Fenton]

We have an application where a certain report must be run by the
bookkeeper on the last day of the month. If that day is a Saturday,
Sunday or holiday, the report is run the next business day. This
report has to be run before anyone updates the database with any new
information on the clients.

What I want to do is, when the application is started, check to see if
today is the last day of the month and if it is, alert the user to not
do any edits until the bookkeeper prints her report. Once she prints
her report, I can set a "yes" flag in a table that it's been done and
the warning won't show up anymore. Sometime after the 1st of the
month I would set that flag back to "no".

I can probably figure out how to determine if today is the last day of
the month, but I'm not sure how to deal with the weekend/holiday issue
and the issue of Leap Years.

I could use some direction as to how to attack this problem.


Paul Fenton

 

[Guest]

We use a holiday table to handle holidays. Our business day
function uses VBA WeekDay function to see if the date is
Saturday or Sunday, (Moves to the next business day, checks
to see if that is in the same month, if not moves backwards
one day at a time, checking for weekends), and then using
Dlookup to check to see if the date is in the holiday table.

Of course you have to put the holidays into the table for this
to work.

The last day of this month is
lastday = DateSerial(Year(date),Month(date)+1,-1)

You will have to check forward each day, because as well
as knowing if this is the last day, you will want to know if
the next business day is after the last day. Most days you
can stop after checking that (1) today is not last day and
(2) today is not friday and (3) tommorow is not a holiday

(david)

 

[Graham Mandeno]

Hi Paul/David

Minor correction - I suspect a typo, David

The last day of this month is
lastday = DateSerial(Year(date),Month(date)+1,-1)

The last argument should be 0 not -1.

The zeroth day of the month is the day before the first day, i.e. the last
day of the previous month.

 

[Paul Fenton]

David, thank you for the response. This gives me a starting point.

Can you give me a little more information on your Holiday table? I
assume that you have to maintain it for the movable dates such as
Thanksgiving, Labor Day, etc. When you do that, do you use the full
date such as mm/dd/yyyy or just month and day? Is it something like:

New Years 1/1
Memorial Day 5/28
Christmas 12/25
etc.


Paul Fenton

 

[Guest]

Our holiday table has a date field, a text field, a country field,
and an autonumber primary key field. and some other stuff.

We have a separate entry for Christmas in every year, in
every country. (Most users only maintain a few countries).
In most countries there are only few fixed date holidays
anyway: lunar holidays like Easter, or 1st Tuesday in November
are more common, and the fixed holidays are the easy ones
anyway.

We don't support state/province holidays. No one has missed
that feature. Local people put their own holidays into the
national table (so they don't work), and ignore other state/province
holidays (because the financial system continues to work on
those days)

(david)

 

[Paul Fenton]

Thanks, David.

Our holiday table has a date field, a text field, a country field,
and an autonumber primary key field. and some other stuff.

We have a separate entry for Christmas in every year, in
every country. (Most users only maintain a few countries).
In most countries there are only few fixed date holidays
anyway: lunar holidays like Easter, or 1st Tuesday in November
are more common, and the fixed holidays are the easy ones
anyway.

We don't support state/province holidays. No one has missed
that feature. Local people put their own holidays into the
national table (so they don't work), and ignore other state/province
holidays (because the financial system continues to work on
those days)

(david)