count work days only
Posted by Roni on July 17, 2001 7:20 AM
I'm wonder if Excel could calculate dates from one date to another,
but only using WORKDAYS (no weekends, no holidays.)
Also, can Excel subtract 3 months and give me the correcsponding
date (no weekends, no holidays)?
Example: I want to put January in a column. I want the next column
to calculate THREE MONTH prior (and the 15th of the month, unless the 15th'
is a weekend or holiday, then I want it to give the closest weekday).
In the next column, I want Excel to calculate 2 weeks from the "15th" date.
I'm sure Excel can do this. What can't Excel do (the dishes)! Can you
help me out.
Thanks.
Posted by Mark W. on July 17, 2001 7:25 AM
Checkout Excel's EDATE() and NETWORKDAYS() functions.
Posted by Roni on July 17, 2001 7:55 AM
I can't figure out how to use either of those. I can't find
them in EXCEL's formula list. Can you provide a little more
guidance. Thank you.
Posted by Mark W. on July 17, 2001 8:00 AM
If these functions is not available, run the
Setup program to install the Analysis ToolPak.
After you install the Analysis ToolPak, you must
enable it by using the Add-Ins command on the
Tools menu.
Posted by Mark W. on July 17, 2001 8:17 AM
I are a college graduate! : ) If these functions ARE not available, run the
Posted by Roni on July 17, 2001 9:36 AM
First, why "college graduate" post?
Second, I figured out how to calculate 3 months prior with EDATE. But NETWORKDAYS
seems to want to count the number of workdays between two dates. I need it to calculate
what 10 working days from a certain would be. Any ideas?
And thanks for the ADD IN info. That worked like a breeze!
Posted by Mark W. on July 17, 2001 11:42 AM
> why "college graduate" post?
I used embarrassingly poor grammar in an earlier posting.
> But NETWORKDAYS...
Originally, I thought that the NETWORKDAYS() function's
recognition of holidays might be of value. I no longer
cling to that belief.
> ...I want it to give the closest weekday.
What exactly to you mean by closest? If EDATE()
produces a date that falls on Saturday do you want
to return a Friday date (-1 offset) or will a Monday
date (+2 offset) suffice? Can the offset ALWAYS be
either postive or negative, but not both? Keep in
mind that the absolute closest work day date is
considerably more difficult to calculate in
conjuction with holidays. First, why "college graduate" post?
Posted by Aladin Akyurek on July 17, 2001 11:53 AM
I used embarrassingly poor grammar in an earlier posting. > But NETWORKDAYS... Originally, I thought that the NETWORKDAYS() function's
Mark -- Why is that? I remember suggesting NETWORKDAYS (which accept a range filled up holidays that one wants to exclude) once and getting a comment that it doesn't compute right at certain dates (forgotten already which dates these were). > ...I want it to give the closest weekday. What exactly to you mean by closest? If EDATE()
=WORKDAY(a-date,10), where "a-date" itself can be, if needed, a date computed by EDATE. : And thanks for the ADD IN info. That worked like a breeze!
Posted by Roni on July 17, 2001 12:01 PM
CLOSEST: yes I mean the closest. If answers lands on a SAturday, I need
it to change to Friday, if Sunday, then Monday. If Monday holiday, then
Tuesday... If some obscure middle of the week holiday (Do we have anymore
of those/???), then the following day. > why "college graduate" post? I used embarrassingly poor grammar in an earlier posting. > But NETWORKDAYS... Originally, I thought that the NETWORKDAYS() function's > ...I want it to give the closest weekday. What exactly to you mean by closest? If EDATE() : And thanks for the ADD IN info. That worked like a breeze!
Posted by Mark W. on July 17, 2001 12:05 PM
> Mark -- Why is that? I remember suggesting NETWORKDAYS (which accept a range filled up holidays that one wants to exclude)
> once and getting a comment that it doesn't compute right at certain dates (forgotten already which dates these were).
Because NETWORKDAYS() doesn't help you find the next
workday which is want Roni wants.
Posted by Mark W. on July 17, 2001 12:10 PM
Then my recommendation would be to construct a lookup
table consisting of Saturday, Sunday and Holiday dates
with their respective offsets. What do you think this
idea? CLOSEST: yes I mean the closest. If answers lands on a SAturday, I need
Posted by Aladin Akyurek on July 17, 2001 12:19 PM
WORKDAY...
:
WORKDAY accepts just as NETWRKDAYS an optional 3rd arg that is a ref to a range which lists the holidays to exclude:
=WORKDAY(EDATE(a-date,n), 10, HOLIDAYS)
where a-date is a date of interest, n number of months, and HOLIDAYS the name of the range that lists the holiday dates.
Is that not what Roni is looking for?
Aladin
Posted by Roni on July 17, 2001 12:34 PM
I think it sounds like a great idea...but I have no idea how
to construct such a thing. (I'm an EXCEL novice, or at least
I feel like one now!)
If you be so kind, please write the formula I would use to:
(Let's assume I have the date January 15, 2002 in cell A1)
1)In next column, Calculate the date 3 months prior to jan 15, but if a weekend
or holiday want it to give CLOSEST weekday.
2)In next column,calculate 10 workdays from resulting calculation from above,
again with result only being a weekday.
Does this make sense? Can you help me out?
Thanks, again!
Posted by Mark W. on July 17, 2001 12:50 PM
Here's a table that embodies your rules:
01/01/01 1
01/02/01 0
01/06/01 -1
01/07/01 1
01/08/01 0
01/13/01 -1
01/14/01 1
01/15/01 0
01/20/01 -1
01/21/01 1
01/22/01 0
01/27/01 -1
01/28/01 1
01/29/01 0
02/03/01 -1
02/04/01 1
02/05/01 0
02/10/01 -1
02/11/01 1
02/12/01 0
02/17/01 -1
02/18/01 1
02/19/01 0
02/24/01 -1
02/25/01 1
02/26/01 0
03/03/01 -1
03/04/01 1
03/05/01 0
03/10/01 -1
03/11/01 1
03/12/01 0
03/17/01 -1
03/18/01 1
03/19/01 0
03/24/01 -1
03/25/01 1
03/26/01 0
03/31/01 -1
04/01/01 1
04/02/01 0
04/07/01 -1
04/08/01 1
04/09/01 0
04/14/01 -1
04/15/01 1
04/16/01 0
04/21/01 -1
04/22/01 1
04/23/01 0
04/28/01 -1
04/29/01 1
04/30/01 0
05/05/01 -1
05/06/01 1
05/07/01 0
05/12/01 -1
05/13/01 1
05/14/01 0
05/19/01 -1
05/20/01 1
05/21/01 0
05/26/01 -1
05/27/01 2
05/28/01 1
05/29/01 0
06/02/01 -1
06/03/01 1
06/04/01 0
06/09/01 -1
06/10/01 1
06/11/01 0
06/16/01 -1
06/17/01 1
06/18/01 0
06/23/01 -1
06/24/01 1
06/25/01 0
06/30/01 -1
07/01/01 1
07/01/01 1
07/02/01 0
07/04/01 1
07/05/01 0
07/07/01 -1
07/08/01 1
07/09/01 0
07/14/01 -1
07/15/01 1
07/16/01 0
07/21/01 -1
07/22/01 1
07/23/01 0
07/28/01 -1
07/29/01 1
07/30/01 0
08/04/01 -1
08/05/01 1
08/06/01 0
08/11/01 -1
08/12/01 1
08/13/01 0
08/18/01 -1
08/19/01 1
08/20/01 0
08/25/01 -1
08/26/01 1
08/27/01 0
09/01/01 1
09/01/01 -1
09/02/01 2
09/03/01 1
09/04/01 0
09/08/01 -1
09/09/01 1
09/10/01 0
09/15/01 -1
09/16/01 1
09/17/01 0
09/22/01 -1
09/23/01 1
09/24/01 0
09/29/01 -1
09/30/01 1
10/01/01 0
10/06/01 -1
10/07/01 1
10/08/01 0
10/13/01 -1
10/14/01 1
10/15/01 0
10/20/01 -1
10/21/01 1
10/22/01 0
10/27/01 -1
10/28/01 1
10/29/01 0
11/03/01 -1
11/04/01 1
11/05/01 0
11/10/01 -1
11/11/01 1
11/12/01 0
11/17/01 -1
11/18/01 1
11/19/01 0
11/22/01 -1
11/23/01 -2
11/24/01 2
11/25/01 1
11/26/01 0
12/01/01 -1
12/02/01 1
12/03/01 0
12/08/01 -1
12/09/01 1
12/10/01 0
12/15/01 -1
12/16/01 1
12/17/01 0
12/22/01 -1
12/23/01 1
12/24/01 2
12/25/01 1
12/26/01 0
12/29/01 -1
12/30/01 1
12/31/01 0
01/01/02 #N/A
I don't know what your holidays are so I used ours:
01/01/01
05/28/01
07/04/01
09/03/01
11/22/01
11/23/01
12/24/01
12/25/01
This table can be used with the following formula
to achieve your desired results:
=EDATE(A1,-3)+VLOOKUP(EDATE(A1,-3),Table,2)
Posted by Mark W. on July 17, 2001 1:05 PM
Re: WORKDAY...
> Is that not what Roni is looking for?
Roni wants the workday that's closest to the EDATE
produced date. This means you'd have to evaluate...
=WORKDAY(EDATE(A1,-3),-1,{"1/1/01", "5/28/01", "7/4/01", "9/3/01", "11/22/01", "11/23/01", "12/24/01", "12/25/01"} + 0)
...and
=WORKDAY(EDATE(A1,-3),1,{"1/1/01", "5/28/01", "7/4/01", "9/3/01", "11/22/01", "11/23/01", "12/24/01", "12/25/01"} + 0)
...to determine which is closest to EDATE(A1,-3),
and if there's a tie use...
=WORKDAY(EDATE(A1,-3),1,{"1/1/01", "5/28/01", "7/4/01", "9/3/01", "11/22/01", "11/23/01", "12/24/01", "12/25/01"} + 0)
YUCK!!!! :
Posted by Mark W. on July 17, 2001 1:11 PM
Also, see my reply below...
I tried to get Roni to settle on either a positive
or negative offset -- not both. She wouldn't...
If she had the solution would have been so much simpler! :
Posted by Aladin Akyurek on July 17, 2001 1:18 PM
Re: WORKDAY...
Mark,
Roni's question is interesting in itself, but I was driven towards the thread by your remark about NETWORKDAYS!
However, if it's immaterial to take into account the holidays and weekends in computing a prior or future date with EDATE, the following could also be used (but I'm never sure about results whenever time or date is the issue):
In B1:
=IF(WEEKDAY(EDATE(A1,-3))=6, EDATE(A1,-3)-1, IF(WEEKDAY(EDATE(A1,-3))=7, EDATE(A1,-3)+1, EDATE(A1,-3))) [ Computing the EDATE part multiple times is not a good idea; it should have a cell of its own. ]
In C1:
=WORKDAY(B1,10, Table) [ where Table contains the holiday dates ]
What do you think?
Aladin
=========
> Is that not what Roni is looking for? Roni wants the workday that's closest to the EDATE
Posted by Mark W. on July 17, 2001 1:46 PM
Re: WORKDAY...
> What do you think?
There are cases where weekends and holidays combine
to make a single contiguous block of non-workdays that
influence the decision to move backwards or forwards
in the pursuit of the "closest" workday. For example...
12/22 - Saturday
12/23 - Sunday
12/24 - Christmas Eve
12/25 - Christmas Day
If EDATE produced 12/23 I presume your method would 1st resolve
to 12/24 then recognize it as a holiday and advance to 12/26.
(I say, "presume" because if the 2nd WEEKDAY argument is omitted
Sunday is 1, but you seem to be using return_type 3). Roni's
"closest" solution suggests that Friday, 12/21 is the solution. Mark, Roni's question is interesting in itself, but I was driven towards the thread by your remark about NETWORKDAYS! However, if it's immaterial to take into account the holidays and weekends in computing a prior or future date with EDATE, the following could also be used (but I'm never sure about results whenever time or date is the issue): In B1: =IF(WEEKDAY(EDATE(A1,-3))=6, EDATE(A1,-3)-1, IF(WEEKDAY(EDATE(A1,-3))=7, EDATE(A1,-3)+1, EDATE(A1,-3))) [ Computing the EDATE part multiple times is not a good idea; it should have a cell of its own. ] In C1: =WORKDAY(B1,10, Table) [ where Table contains the holiday dates ] What do you think? Aladin ========= > Is that not what Roni is looking for? : Roni wants the workday that's closest to the EDATE
Posted by Mark W. on July 17, 2001 1:55 PM
** Revised ** Table
Roni, found a defect in my previous table listing.
It's very important that you validate this table
before you rely on it!
Here's a table that embodies your rules:
01/01/01 1
01/02/01 0
01/06/01 -1
01/07/01 1
01/08/01 0
01/13/01 -1
01/14/01 1
01/15/01 0
01/20/01 -1
01/21/01 1
01/22/01 0
01/27/01 -1
01/28/01 1
01/29/01 0
02/03/01 -1
02/04/01 1
02/05/01 0
02/10/01 -1
02/11/01 1
02/12/01 0
02/17/01 -1
02/18/01 1
02/19/01 0
02/24/01 -1
02/25/01 1
02/26/01 0
03/03/01 -1
03/04/01 1
03/05/01 0
03/10/01 -1
03/11/01 1
03/12/01 0
03/17/01 -1
03/18/01 1
03/19/01 0
03/24/01 -1
03/25/01 1
03/26/01 0
03/31/01 -1
04/01/01 1
04/02/01 0
04/07/01 -1
04/08/01 1
04/09/01 0
04/14/01 -1
04/15/01 1
04/16/01 0
04/21/01 -1
04/22/01 1
04/23/01 0
04/28/01 -1
04/29/01 1
04/30/01 0
05/05/01 -1
05/06/01 1
05/07/01 0
05/12/01 -1
05/13/01 1
05/14/01 0
05/19/01 -1
05/20/01 1
05/21/01 0
05/26/01 -1
05/27/01 2
05/28/01 1
05/29/01 0
06/02/01 -1
06/03/01 1
06/04/01 0
06/09/01 -1
06/10/01 1
06/11/01 0
06/16/01 -1
06/17/01 1
06/18/01 0
06/23/01 -1
06/24/01 1
06/25/01 0
06/30/01 -1
07/01/01 1
07/02/01 0
07/04/01 1
07/05/01 0
07/07/01 -1
07/08/01 1
07/09/01 0
07/14/01 -1
07/15/01 1
07/16/01 0
07/21/01 -1
07/22/01 1
07/23/01 0
07/28/01 -1
07/29/01 1
07/30/01 0
08/04/01 -1
08/05/01 1
08/06/01 0
08/11/01 -1
08/12/01 1
08/13/01 0
08/18/01 -1
08/19/01 1
08/20/01 0
08/25/01 -1
08/26/01 1
08/27/01 0
09/01/01 1
09/01/01 -1
09/02/01 2
09/03/01 1
09/04/01 0
09/08/01 -1
09/09/01 1
09/10/01 0
09/15/01 -1
09/16/01 1
09/17/01 0
09/22/01 -1
09/23/01 1
09/24/01 0
09/29/01 -1
09/30/01 1
10/01/01 0
10/06/01 -1
10/07/01 1
10/08/01 0
10/13/01 -1
10/14/01 1
10/15/01 0
10/20/01 -1
10/21/01 1
10/22/01 0
10/27/01 -1
10/28/01 1
10/29/01 0
11/03/01 -1
11/04/01 1
11/05/01 0
11/10/01 -1
11/11/01 1
11/12/01 0
11/17/01 -1
11/18/01 1
11/19/01 0
11/22/01 -1
11/23/01 -2
11/24/01 2
11/25/01 1
11/26/01 0
12/01/01 -1
12/02/01 1
12/03/01 0
12/08/01 -1
12/09/01 1
12/10/01 0
12/15/01 -1
12/16/01 1
12/17/01 0
12/22/01 -1
12/23/01 -2
12/24/01 2
12/25/01 1
12/26/01 0
12/29/01 -1
12/30/01 1
12/31/01 0
01/01/02 #N/A
I don't know what your holidays are so I used ours:
01/01/01
05/28/01
07/04/01
09/03/01
11/22/01
11/23/01
12/24/01
12/25/01
This table can be used with the following formula
to achieve your desired results:
=EDATE(A1,-3)+VLOOKUP(EDATE(A1,-3),Table,2)
where "Table" is either a named range or a cell
reference for the table listed above.
Posted by Mark W. on July 17, 2001 2:01 PM
Make that return_type 2...
There are cases where weekends and holidays combine
Posted by Aladin Akyurek on July 17, 2001 2:37 PM
Re: Make that return_type 2...
It was my intention to have that optional return arg: From =6 and =7, that intention is clear, but not carried out. Thanks pointing that out.
The formula in B1 must indeed be:
=IF(WEEKDAY(EDATE(A1,-3),2)=6, EDATE(A1,-3)-1,IF(WEEKDAY(EDATE(A1,-3), 2)=7, EDATE(A1,-3)+1, EDATE(A1,-3)))
The formula in C1 is:
=WORKDAY(B1,10, Table) [ where Table contains your holiday dates. ]
In column D, I used the single formula that I suggested before the above set of 2 formulas:
=WORKDAY(EDATE(A1,-3),10,Table) [ used your Table of holidays ]
Here is what I get:
{35449, 35357, 35371, 35371;35785, 35693, 35707, 35707;35786, 35696, 35710, 35707;35787, 35696, 35710, 35710;35788, 35697, 35711, 35711;35789, 35698, 35712, 35712;35790, 35699, 35713, 35713;35791, 35700, 35714, 35714}
There is one disagreement between the formulas in C and in D:
Given 23-Dec-2001, the formula in C gives 8-Oct-01, the one in D 5-Oct-01. I have no clue which is correct.
Aladin
========= : > What do you think? : There are cases where weekends and holidays combine
