Networkday Calendar
 

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

Networkday Calendar
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503599

Networkday Calendar
 

daddylonglegs wrote:
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


That worked great. It seamed so simple. Thanks for the reply.

rac