PDA

View Full Version : Calculate future date w/ out weekends and holidays



hatched850
06-27-2006, 07:08 AM
Excel 2003 I need to caculate a future date 20 days in advance minus weekends and holidays. This is way out of my League. Can someone help me?:dunno I can do the 20 days but have no clue how to take out the weekends and holidays.

Bob Phillips
06-27-2006, 08:52 AM
Excel 2003 I need to caculate a future date 20 days in advance minus weekends and holidays. This is way out of my League. Can someone help me?:dunno I can do the 20 days but have no clue how to take out the weekends and holidays.

=WORKDAY(start_date,num_days,holidays)

where holidays is a range of dates.

hatched850
06-27-2006, 09:11 AM
Like this?? =WORKDAY(B2,20,9/4/6) I am getting #NAME?

Bob Phillips
06-27-2006, 09:38 AM
Like this?? =WORKDAY(B2,20,9/4/6) I am getting #NAME?
No like this

=WORKDAY(B2,20,--"2006-09-04")

but far better to put the holidays in a worksheet range, you can have more than one then.

jungix
06-28-2006, 05:07 AM
There is no other way of doing this? Because creating a list each time may cause some trouble. I have the same problem, as I need the date of the next open day, so usually today+1, but today+3 on Fridays. Can't we just test the day of the week it is?

Bob Phillips
06-28-2006, 05:37 AM
The list is just a list of holidays which will apply forevere. You don't need to add the weekend days in. If you want to ignore holidays, just use

=WORKDAY(TODAY(),1)

or if you must,

=TODAY()+IF(WEEKDAY(TODAY(),1)=6,3,1)

jungix
06-28-2006, 06:05 AM
Thank you very much! You guys are just geniuses

hatched850
06-28-2006, 07:47 AM
Putting it in as you have it I still get the same message =WORKDAY(B2,20,--"2006-09-04") #NAME?

lucas
06-28-2006, 07:54 AM
You must have analysis toolpak installed I think for this to work

hatched850
06-28-2006, 08:18 AM
Can I get this on Microsoft?

lucas
06-28-2006, 08:29 AM
Comes with it. In excel just go to tools-Addins and select the Analysis tool Pak addin. If you did not install it when you put Office on your system you will be prompted for the installation disk.