Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Calendar issue | Excel Discussion (Misc queries) | |||
Erasing Outlook Calendar from within Excel | Excel Discussion (Misc queries) | |||
How do i set up a calendar in excel? | Excel Discussion (Misc queries) | |||
Calendar and info for that date | Excel Discussion (Misc queries) |