Calculate A Date Excluding Weekends And Holidays
 

I have a date and a series of tasks that need to be completed by a
certain number of days from the intial date. The time to complete the
tasks can not include weekends or holidays.
Example: Initial date is 2/15/2006
I have 10 business days to complete a task

All Help will be appreciated. Thank you.


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

=WORKDAY(A1,10)

Format as date
--
Kind regards,

Niek Otten

"travelersway"
wrote in message
news:travelersway.22wy5a_1139406608.6858@excelforu m-nospam.com...

I have a date and a series of tasks that need to be completed by a
certain number of days from the intial date. The time to complete the
tasks can not include weekends or holidays.
Example: Initial date is 2/15/2006
I have 10 business days to complete a task

All Help will be appreciated. Thank you.


--
travelersway
------------------------------------------------------------------------
travelersway's Profile:
http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

NIEK,
=WORKDAY(A1,10)
I tried that formula and the results came up as NAME.
Any suggestiond?Thanks,

TRAVELERSWAY


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

Sounds like you're missing the Analysis TookPak Add-in. Excel's Help on
the WORKDAY function says the following:

1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available list, select the Analysis ToolPak box, and
then click OK.
3. If necessary, follow the instructions in the setup program.


--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

TAYLORM,

I followed your suggestion. I now receive the same day but an extended
year: calculated date of 2/15/*20 *.

I am using a custom format for the date; xx"/"xx"/"xx will this affect
the results?

Thank you for your help.

TRAVELERSWAY.


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

When I changed mine to a Custom format of xx"/"xx"/"xx as you said, it
displayed xx/xx/xx. So I'm not sure exactly what you're trying to do
there.:confused:

I have 2/15/2006 in cell A1, with a normal Date type format. In cell
B2 I have the following formula:
=WORKDAY(A1,10) to add 10 working days to 2/15/2006.

The result in cell B2 (with the same Date type format as cell A1) shows
3/1/2006.


--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

Yes, to make sense WORKDAY needs a date entered with delimiters like
02/01/06 for Feb 2006 using US settings
or it needs the conversion whatever that might be depending on how you enter
"dates"
Note that it is always better to enter dates that excel can recognize
instead of enter pseudo dates that one has to convert to excel dates

--
Regards,

Peo Sjoblom

Portland, Oregon




"travelersway"
wrote in message
news:travelersway.22x7vb_1139419202.7497@excelforu m-nospam.com...

TAYLORM,

I followed your suggestion. I now receive the same day but an extended
year: calculated date of 2/15/*20 *.

I am using a custom format for the date; xx"/"xx"/"xx will this affect
the results?

Thank you for your help.

TRAVELERSWAY.


--
travelersway
------------------------------------------------------------------------
travelersway's Profile:
http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

TAYLORM,

Thank you for hour help. The date is programed to just enter the
numbers for the date: 021506 would = 2/15/06. I'll reformat the date
funcion and see what happens.

Will this formula account for legal holidays as well?

Thanks again for all your help.
TRAVELERSWAY


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

Yes, you can tell it to account for holidays. There is a 3rd parameter
in the function where you can either reference a range of cells that
contain holiday dates or you can enter an array constant.

The Excel Help gives an excellent example.

If I change my formula to =WORKDAY(A1,11,$C$1:$C$10), where C1 thru C10
contain holiday dates, my result changes from 3/2/2006 to 3/3/2006
(because I have 2/20/2006 listed in my holiday range).


--
taylorm
------------------------------------------------------------------------
taylorm's Profile: http://www.excelforum.com/member.php...o&userid=28892
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Calculate A Date Excluding Weekends And Holidays
 

I CHANGED THE FORMAT FOR THE DATES AND EVERYTHING FELL INTO PLACE!

I"ll attempt that holiday array.

Again,Thanks to everyone for the help.


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=510032

Excellent thread, very helpful.... thank you!
Now, how can I calculate a date that can land on any day of the week BUT will exclude holidays?

I tried using the NETWORKDAYS function (similar to how the WORKDAYS function was used above) but received #### which a pop-up Excel box indicates is "either an negative or too large to display." Any idea why this isn't working?