Closed Thread
Results 1 to 9 of 9

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

  1. #1
    Dark Skunk
    Guest

    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

  2. #2
    Jason Morin
    Guest

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

    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
    >.
    >


  3. #3
    Dark Skunk
    Guest

    Re: Is there a WorkDay() type function that count all days except

    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

  4. #4
    Jason Morin
    Guest

    Re: Is there a WorkDay() type function that count all days except

    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
    >.
    >


  5. #5
    Ron Rosenfeld
    Guest

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

    On Fri, 11 Feb 2005 11:27:02 -0800, "Dark Skunk" <Dark
    Skunk@discussions.microsoft.com> 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

  6. #6
    Myrna Larson
    Guest

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

    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
    Skunk@discussions.microsoft.com> 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



  7. #7
    Dark Skunk
    Guest

    Re: Is there a WorkDay() type function that count all days except

    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
    > >.
    > >

    >


  8. #8
    Dark Skunk
    Guest

    Re: Is there a WorkDay() type function that count all days except

    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
    > >.
    > >

    >


  9. #9
    Dark Skunk
    Guest

    Re: Is there a WorkDay() type function that count all days except

    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
    > >.
    > >

    >


Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1