Workday/Holiday
Posted by Kerri on January 31, 2002 8:28 AM
1. How do you claculate two business days from another date, and excluding holidays?
2. Also, how do you subtract one date from another and show the anwer in number form and in business days,and excluding holidays?
Example 1: Date Sent = 1/18/02 and Expected Received Date = +2 business days/exclude holiday = 1/24/02.
Example 2: Date Received 1/24/02 - Date Sent 1/18/02 = 2.
Posted by Steve Hartman on January 31, 2002 8:38 AM
Use =NETWORKDAY() function, found in the analysis toolpak.
Posted by Steve Hartman on January 31, 2002 8:40 AM
correction
That should be =NETWORKDAYS()
Posted by Aladin Akyurek on January 31, 2002 10:25 AM
For both sort of questions it would be practical to list the holday dates in a column range of its own. I'd suggest naming this range HOLIDAYS via the Name Box.
Question 1:
=WORKDAY(A1,2,HOLIDAYS)
will give you an expected Received Date (A1 houses a Date Sent).
Question 2:
=NETWORKDAYS(A1,B1,HOLIDAYS)-1
will give the number of business days that elapsed between a Date Sent (A1) and a Date Received (B1). I derived the need for Minus 1 from your "Example 2".
Both functions are available from the Anaysis Toolpak which, if needed, can be added in via Tools|Add-Ins.
