Solved
Contributor
Posts: 30

# Excel Network Day's function

So I was browsing the SAS for Dummies blog and came across this post.

http://blogs.sas.com/content/sasdummy/2011/05/09/calculating-the-number-of-working-days-between-two-...

Which I thought was interesting, but I was wondering if it could be improved.

Some holidays are set, like January 1st, fourth of July, veteran's day, and always occur on the same day each year. But others are determined by weekday of the month rather than date.

My office has a set list of holidays which comes down to,
Jan 1st,

Third Monday of January

Last Monday of May (could be the fourth or fifth monday depending on year)

July 4th

First Monday of Sept

November 11th

4th Thursday of November

December 25th.

(And if 01/01, 07/04, 11/11, or 12/25 is ever on a Sunday, then the holiday is observed on the following Monday.)

The custom function in the blog post would require someone to update the table with the holiday dates for all these variable holidays every year. Is there a way to have a program automatically create a volatile table to calculate for these dates that would then be used in the custom function?

Accepted Solutions
Solution
‎06-03-2016 04:04 PM
Super User
Posts: 13,563

## Re: Excel Network Day's function

For typical US and Canadaian holidays at least, which you list looks like you may want to investigate the Holiday function.

You give the function the name of the holiday and the year and function returns a SAS date value for the given holiday:

``````data _Null_;
LaborDay = Holiday('Labor',2016);
put LaborDay mmddyy10.;
run;``````

For example.

For those that may be observed on different days other than those that the function has designed it isn't too dificult to adjust using the

WEEKDAY function

If weekday(holiday('christmas',2016))=1 then ChristmasObserved= holiday('christmas',2016) +1;

(sunday is weekday 1, saturday weekday 7)

All Replies
Solution
‎06-03-2016 04:04 PM
Super User
Posts: 13,563

## Re: Excel Network Day's function

For typical US and Canadaian holidays at least, which you list looks like you may want to investigate the Holiday function.

You give the function the name of the holiday and the year and function returns a SAS date value for the given holiday:

``````data _Null_;
LaborDay = Holiday('Labor',2016);
put LaborDay mmddyy10.;
run;``````

For example.

For those that may be observed on different days other than those that the function has designed it isn't too dificult to adjust using the

WEEKDAY function

If weekday(holiday('christmas',2016))=1 then ChristmasObserved= holiday('christmas',2016) +1;

(sunday is weekday 1, saturday weekday 7)

🔒 This topic is solved and locked.