Networkday Calendar

R

rac

I'm trying to create calendar with just networkdays minus Holidays.
I copied this function from Chip Pearson's web site (Thanks Chip).
=IF(OR(WEEKDAY(A4+1)=1,WEEKDAY(A4+1)=7),A4+3,A4+1)
It works great but, I would like to exclude a list of holidays. I have
the holidays in a range named "holidays". Any ideas on how to do this?

rac
 
D

daddylonglegs

I'd never seen that formula before so I looked it up on Chip's website
to check and it is as you say it is.

I think it works fine until you put a Saturday in A4 in which case the
next date it shows is a Tuesday! Perhaps I'll take it up with Chip!

I prefer this

=A4+IF(WEEKDAY(A4)<6,1,9-WEEKDAY(A4))

...but that still doesn't address your question about holidays....

The simple way to do what you ask is to use the WORKDAY function
available with Analysis ToolPak add-in, the formula would be

=WORKDAY(A4,1,holidays)

If you don't have Analysis ToolPak installed or you don't want to use
it then this slightly more complex formula will work, assuming you
never have more than 5 consecutive holiday days

=MIN(IF(WEEKDAY(A4+ROW($1:$10),2)<6,IF(ISNA(MATCH(A4+ROW($1:$10),holidays,0)),A4+ROW($1:$10))))

confirmed with CTRL+SHIFT+ENTER
 
R

rac

daddylonglegs said:
I'd never seen that formula before so I looked it up on Chip's website
to check and it is as you say it is.

I think it works fine until you put a Saturday in A4 in which case the
next date it shows is a Tuesday! Perhaps I'll take it up with Chip!

I prefer this

=A4+IF(WEEKDAY(A4)<6,1,9-WEEKDAY(A4))

..but that still doesn't address your question about holidays....

The simple way to do what you ask is to use the WORKDAY function
available with Analysis ToolPak add-in, the formula would be

=WORKDAY(A4,1,holidays)

If you don't have Analysis ToolPak installed or you don't want to use
it then this slightly more complex formula will work, assuming you
never have more than 5 consecutive holiday days

=MIN(IF(WEEKDAY(A4+ROW($1:$10),2)<6,IF(ISNA(MATCH(A4+ROW($1:$10),holidays,0)),A4+ROW($1:$10))))

confirmed with CTRL+SHIFT+ENTER
Click to expand...
That worked great. It seamed so simple. Thanks for the reply.

rac
 
Top