Date series excluding holidays

Posted by Mike H on May 04, 2001 7:39 AM

I am trying to create a series of dates in a column. These are based on a given start date in the first cell and a number of months between the dates between each date, as set in another cell.

The dates should, however, exclude Saturdays and Sundays and a set list of holidays as set in a separate range of cells. Where one of these dates would result the cell should contain the next working day.

Is there a reasonably straightforward way of doing this please without using VBA?

Thanks

Mike H

Posted by IML on May 04, 2001 8:08 AM

You can use the =networkdays available on the analysis tool pack. The three arguments are
start date
end date
dates to exclude(ie your range).

good luck

Posted by Aladin Akyurek on May 04, 2001 8:38 AM

Mike,

I'm not too sure, but try this:

In A2 enter: =WORKDAY(EDATE(A1,$B$1),0,HOLIDAYS)

where A1 contains a valid start date, B1 the number of months, and HOLIDAYS is the name of the range where the holidays (data) are listed.

You can name the range of holidays as HOLIDAYS via the Name Box (otherwise use the absolute ref of the range in the formula above).

Copy down the formula as far as needed.

Don't forget to format the cells in column A as date.

Aladin

===============