NETWORKDAYS thru 2020

[Guest]

Is there an easy way to create a list of all non-working days (Sat & Sun,
major holidays) between now and the year 2020? I want to use this range as
the "holidays" argument in the NETWORKDAYS function.

I could create this list by hand, but if it can be done with programming,
all the better! Thanks for any ideas you can give me.

 

[Allen Lance]

Steve,

The easiest solution I can think of is running through a loop
mathematically to identify the Saturdays and Sundays. Start somewhere
like last Saturday then cycle through one week at a time adding 7 days
to identify all the Saturdays and 8 days to identify all the Sundays.
Adding them to a collection as you go. There aren't that many
holidays that eliminate work these days so you could choose to
identify those manually. Once you get rid of the 1352 Sat/Sun dates
over the next 13 years you're job looks a lot more manageable. A loop
can help you tie up the static holidays like new years and Christmas.
The non-static dates like Thanksgiving can be done with a little math
and a few variables. If you load all of these dates into an Array
then you can utiltize them in that function.

HTH

Allen

 

[Guest]

Thanks for the suggestions, Allen. I'll give them a shot.

 

[Ron Rosenfeld]

Is there an easy way to create a list of all non-working days (Sat & Sun,
major holidays) between now and the year 2020? I want to use this range as
the "holidays" argument in the NETWORKDAYS function.

I could create this list by hand, but if it can be done with programming,
all the better! Thanks for any ideas you can give me.

Since NETWORKDAYS already excludes Saturdays and Sundays, I don't understand
why you would want to include those dates in your function.

I think you really only need a list of the holiday dates. Perhaps you could
find such a list on the web.

If not, then some are easy:

New Years
Christmas
July 4.

Some are a little more complicated:

Labor Day
Thanksgiving

Some are probably best done by looking them up in a table:

Easter

Some require knowledge of the rules for the particular corporation:

If Christmas is on a Saturday, is Friday a working day?

A formula for New Years might be:

=DATE(ROWS($1:2008),1,1)

as you copy/drag down, it will give you Jan 1 for the years from 2008 to
however far down you drag it.

Labor Day is the first Monday in September, so:

=DATE(ROWS($1:2007),8,31)-WEEKDAY(DATE(ROWS($1:2007),8,31)+6)+8

and copy/drag down.

and so forth.
--ron

 

[Guest]

Ron,

Thanks for the ideas. I realized going in it probably would take some time
to compile all the dates, but the formulas you gave me will help.