Is there a WorkDay() type function that count all days except tho.

I'm familiar with the WorkDay() function and its almost exactly what I need.
My problem is that I'm planning some dates for a company that works on
weekends. As far as I can tell there is no way to include these dates. I
can take dates out but not put them in. Do you know of a function? Do you
know if I can copy the code for that and update it (not that I'm quite that
good but I could give it a shot). Mike

I can't quite figure out what you're asking. Your subject
line was a good start. Ask your question in the body of
the message and give an example of some data and the
desired result.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I'm familiar with the WorkDay() function and its almost
exactly what I need.
>My problem is that I'm planning some dates for a company
that works on
>weekends. As far as I can tell there is no way to
include these dates. I
>can take dates out but not put them in. Do you know of
a function? Do you
>know if I can copy the code for that and update it (not
that I'm quite that
>good but I could give it a shot). Mike
>.
>

Thanks for the reply. Here is my deal: In the function WorkDay(x,y,z)
[tools>add-ins>analysis toolpack] x being a start date, y being your leadtime
in days, and z being a range that removes any holidays you want to consider.
If I put WorkDay(2/10/05 , 4 , 2/14/05) It will start counting 5 days from
2/10 (not including that day) count 1 for 2/11 skip 2/12 and 13 due to
weekend, skip 2/14 due to being on the holiday list and then 2,3,4,5 for
2/15, 2/16, 2/17, 2/18. This provides a final answer of 2/18. I want this
same exact function except I want it to count weekends. Therefore with the
same information above return the date of 2/16. Seems simple enough, it
probably as easy as removing a line or two out of the workday() function
which removes the weekends. What do you think? -Mike

I see. Try:

=A1+B1+SUMPRODUCT(--((E1:E10>=A1)*(E1:E10<=A1+B1)))

where:

A1 = start date
B1 = # of days after the date in A1
E1:E10 = a list of any holiday dates you wish to skip over

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Thanks for the reply. Here is my deal: In the function
WorkDay(x,y,z)
>[tools>add-ins>analysis toolpack] x being a start date,
y being your leadtime
>in days, and z being a range that removes any holidays
you want to consider.
>If I put WorkDay(2/10/05 , 4 , 2/14/05) It will start
counting 5 days from
>2/10 (not including that day) count 1 for 2/11 skip 2/12
and 13 due to
>weekend, skip 2/14 due to being on the holiday list and
then 2,3,4,5 for
>2/15, 2/16, 2/17, 2/18. This provides a final answer of
2/18. I want this
>same exact function except I want it to count weekends.
Therefore with the
>same information above return the date of 2/16. Seems
simple enough, it
>probably as easy as removing a line or two out of the
workday() function
>which removes the weekends. What do you think? -Mike
>.
>

On Fri, 11 Feb 2005 11:27:02 -0800, "Dark Skunk" <Dark
[email protected]> wrote:

>I'm familiar with the WorkDay() function and its almost exactly what I need.
>My problem is that I'm planning some dates for a company that works on
>weekends. As far as I can tell there is no way to include these dates. I
>can take dates out but not put them in. Do you know of a function? Do you
>know if I can copy the code for that and update it (not that I'm quite that
>good but I could give it a shot). Mike

If the company works on weekends, does that mean they work seven days a week?

If so, then just add the number of days to the base date.

So if the Workday formula is =WORKDAY(base_date,30)

you would use just =base_date + 30


--ron

Do you have a 5-day workweek or 7-day workweek? If the former, but the days
off are, say Tue and Wed instead of Sat and Sun, add 3 days to the starting
date, then subtract it at the end, i.e.

=WORKDAY(A1+3,4,Holidays)-3

You would also have to add 3 to each of the dates in the Holiday list.


On Fri, 11 Feb 2005 11:27:02 -0800, "Dark Skunk" <Dark
[email protected]> wrote:

>I'm familiar with the WorkDay() function and its almost exactly what I need.
>My problem is that I'm planning some dates for a company that works on
>weekends. As far as I can tell there is no way to include these dates. I
>can take dates out but not put them in. Do you know of a function? Do you
>know if I can copy the code for that and update it (not that I'm quite that
>good but I could give it a shot). Mike

Jason,
Thanks very much. This will definitely work. Could you explain what the
"--" denotes in the formula? I'm going to sit down with it today and make
100% sure I understand the mechanics before I start using it. On a general
note I believe we are simply adding the Leadtime to the Start date and then
adding a certain number of days on top of that to account for the holidays we
encounter for that period. Thanks again, I'm sure this will do the trick
once I'm comfortable with it. Mike

"Jason Morin" wrote:

> I see. Try:
>
> =A1+B1+SUMPRODUCT(--((E1:E10>=A1)*(E1:E10<=A1+B1)))
>
> where:
>
> A1 = start date
> B1 = # of days after the date in A1
> E1:E10 = a list of any holiday dates you wish to skip over
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Thanks for the reply. Here is my deal: In the function
> WorkDay(x,y,z)
> >[tools>add-ins>analysis toolpack] x being a start date,
> y being your leadtime
> >in days, and z being a range that removes any holidays
> you want to consider.
> >If I put WorkDay(2/10/05 , 4 , 2/14/05) It will start
> counting 5 days from
> >2/10 (not including that day) count 1 for 2/11 skip 2/12
> and 13 due to
> >weekend, skip 2/14 due to being on the holiday list and
> then 2,3,4,5 for
> >2/15, 2/16, 2/17, 2/18. This provides a final answer of
> 2/18. I want this
> >same exact function except I want it to count weekends.
> Therefore with the
> >same information above return the date of 2/16. Seems
> simple enough, it
> >probably as easy as removing a line or two out of the
> workday() function
> >which removes the weekends. What do you think? -Mike
> >.
> >
>

It worked but only to a certain point. For instance. Assuming we did not
work weekends from 1/5 till 2/4 and all other days were work days. If I had
a 20 day Leadtime and enter it into the forumula it returns 1/31 but in fact
it should be 2/2. Is has to do with the way your defining what to count up.
The range which must be counted in the sumproduct changes as holidays are
passed over if you follow me. I'm trying to figure out how to account for
this. Let me know if you have any ideas. Mike.

"Jason Morin" wrote:

> I see. Try:
>
> =A1+B1+SUMPRODUCT(--((E1:E10>=A1)*(E1:E10<=A1+B1)))
>
> where:
>
> A1 = start date
> B1 = # of days after the date in A1
> E1:E10 = a list of any holiday dates you wish to skip over
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Thanks for the reply. Here is my deal: In the function
> WorkDay(x,y,z)
> >[tools>add-ins>analysis toolpack] x being a start date,
> y being your leadtime
> >in days, and z being a range that removes any holidays
> you want to consider.
> >If I put WorkDay(2/10/05 , 4 , 2/14/05) It will start
> counting 5 days from
> >2/10 (not including that day) count 1 for 2/11 skip 2/12
> and 13 due to
> >weekend, skip 2/14 due to being on the holiday list and
> then 2,3,4,5 for
> >2/15, 2/16, 2/17, 2/18. This provides a final answer of
> 2/18. I want this
> >same exact function except I want it to count weekends.
> Therefore with the
> >same information above return the date of 2/16. Seems
> simple enough, it
> >probably as easy as removing a line or two out of the
> workday() function
> >which removes the weekends. What do you think? -Mike
> >.
> >
>

It worked but only to a certain point. For instance. Assuming we did not
work weekends from 1/5 till 2/4 and all other days were work days. If I had
a 20 day Leadtime and enter it into the forumula it returns 1/31 but in fact
it should be 2/2. Is has to do with the way your defining what to count up.
The range which must be counted in the sumproduct changes as holidays are
passed over if you follow me. I'm trying to figure out how to account for
this. Let me know if you have any ideas. Mike.

"Jason Morin" wrote:

> I see. Try:
>
> =A1+B1+SUMPRODUCT(--((E1:E10>=A1)*(E1:E10<=A1+B1)))
>
> where:
>
> A1 = start date
> B1 = # of days after the date in A1
> E1:E10 = a list of any holiday dates you wish to skip over
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Thanks for the reply. Here is my deal: In the function
> WorkDay(x,y,z)
> >[tools>add-ins>analysis toolpack] x being a start date,
> y being your leadtime
> >in days, and z being a range that removes any holidays
> you want to consider.
> >If I put WorkDay(2/10/05 , 4 , 2/14/05) It will start
> counting 5 days from
> >2/10 (not including that day) count 1 for 2/11 skip 2/12
> and 13 due to
> >weekend, skip 2/14 due to being on the holiday list and
> then 2,3,4,5 for
> >2/15, 2/16, 2/17, 2/18. This provides a final answer of
> 2/18. I want this
> >same exact function except I want it to count weekends.
> Therefore with the
> >same information above return the date of 2/16. Seems
> simple enough, it
> >probably as easy as removing a line or two out of the
> workday() function
> >which removes the weekends. What do you think? -Mike
> >.
> >
>