+ Reply to Thread
Results 1 to 13 of 13

WORKDAY function should work without treating saturdays as holidays or other alternat

  1. #1
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    WORKDAY function should work without treating saturdays as holidays or other alternat

    my problem is as under

    sample file uploaded


    Cell A2 has project start date
    Cell B2 has project days
    Cell F1 to F17 has holidays, range named as holidays
    Cell C2 is project end date after calculation

    my problem is that workday function treats saturdays as holidays which in my case should be working days
    sample.xlsx.
    In other words, how should I calculate project end date after treating sundays and specfied holidays only.
    Last edited by anilrini; 03-09-2012 at 06:45 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,801

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    You can use a kind of NETWORKDAYS formula as described at this page

  3. #3
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    many thanks



    Quote Originally Posted by Pepe Le Mokko View Post
    You can use a kind of NETWORKDAYS formula as described at this page

  4. #4
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    But this has slowed down the process because i have to wrote down VBA mod for new formula

  5. #5
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    can any (possibly an array formula) formula be written instead of VB module
    Last edited by anilrini; 03-02-2012 at 12:15 AM.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,458

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    Pl see the attached file. UDF Workday2 is defined which counts saturdays and holidays are taken into account.

    Please Login or Register  to view this content.
    Clarifications Welcome.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    anybody please
    i want this to be used as an array formula with existing functions only

  8. #8
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    thanks but i want this as an formula with existing functions

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl see the attached file. UDF Workday2 is defined which counts saturdays and holidays are taken into account.

    Please Login or Register  to view this content.
    Clarifications Welcome.

  9. #9
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    you can use this, where range "F:F" is your holidays and range "G:G" is your rest days (or sundays)

    =A2+B2+COUNTIFS(F:F,"<="&A2+B2,F:F,">="&A2)+COUNTIFS(G:G,"<="&A2+B2,G:G,">="&A2)
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  10. #10
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    Thanks
    I assume that one has to write all sundays in G:G to work this. But if i had to do this i can achieve it with this formula

    =C2+SMALL(IF(COUNTIF(holidays,C2+ROW(INDIRECT("1:"&D2+COUNT(holidays)))),"",ROW(INDIRECT("1:"&D2 +COUNT(holidays)))),D2)

    confirmed with CTRL+SHIFT+ENTER

    where C2 has start date, D2 has number of days and holidays is a named range for holidays. (i can include all sundays in this named range too)

    But i do not want to list all sundays.


    Quote Originally Posted by DGagnon View Post
    you can use this, where range "F:F" is your holidays and range "G:G" is your rest days (or sundays)

    =A2+B2+COUNTIFS(F:F,"<="&A2+B2,F:F,">="&A2)+COUNTIFS(G:G,"<="&A2+B2,G:G,">="&A2)

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    Apologies anilrini, I meant to reply to your query elsewhere but kept forgetting....

    I don't believe that approach will work DGagnon, even if you list all Sundays. The formula finds all Sundays and holidays between A2 and A2+B2 and adds those to A2+B2.....but that doesn't account for additional holidays or Sundays that might be between A2+B2 and that new date.

    Try this "array formula"

    =SMALL(IF(WEEKDAY(A2+ROW(INDIRECT("1:"&B2*10)))<>1,IF(COUNTIF(holidays,A2+ROW(INDIRECT("1:"&B2*10)))=0, ROW(INDIRECT("1:"&B2*10)))),B2)+A2

    confirmed with CTRL+SHIFT+ENTER

    This assumes you will never have as many as 10 non-workdays consecutively
    Last edited by daddylonglegs; 03-02-2012 at 06:17 PM.
    Audere est facere

  12. #12
    Registered User
    Join Date
    02-28-2012
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    thanks
    i think if this assumption has to varied i.e. assuming that not more than 15 holidays/closing days consecutively, we may change the formula by replacing 10 with 15.

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: WORKDAY function should work without treating saturdays as holidays or other alte

    That's right, or you can replace 10 with COUNT(holidays)*2

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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