+ Reply to Thread
Results 1 to 19 of 19

Date formula that functions like WORKDAY but includes weekends

  1. #1
    Registered User
    Join Date
    06-24-2011
    Location
    Sunny Florida
    MS-Off Ver
    Excel 2003
    Posts
    6

    Date formula that functions like WORKDAY but includes weekends

    Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days. Any help would be greatly appreciated, as this is a project for work.

    I am using Excel 2010.

    Thank you!

  2. #2
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Date formula that functions like WORKDAY but includes weekends

    I assume you still want to consider holidays? If not, then simply adding 5 to the originial date will do the trick.

    =A1 + 5

  3. #3
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Date formula that functions like WORKDAY but includes weekends

    =C2+D2+SUMPRODUCT(--(C2<$A$2:$A$4),--(C2+D2>$A$2:$A$4))

    This assumes your holidays are in A2:A4:, your start date is in C2 and your number of days to add is in D2

    See the attached for an example.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-24-2011
    Location
    Sunny Florida
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Date formula that functions like WORKDAY but includes weekends

    Quote Originally Posted by Whizbang View Post
    =C2+D2+SUMPRODUCT(--(C2<$A$2:$A$4),--(C2+D2>$A$2:$A$4))

    This assumes your holidays are in A2:A4:, your start date is in C2 and your number of days to add is in D2

    See the attached for an example.
    thank you sooooooo much! It worked perfectly!

  5. #5
    Registered User
    Join Date
    06-24-2011
    Location
    Sunny Florida
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Date formula that functions like WORKDAY but includes weekends

    Quote Originally Posted by Whizbang View Post
    I assume you still want to consider holidays? If not, then simply adding 5 to the originial date will do the trick.

    =A1 + 5
    I have to exclude holidays & dates we are closed otherwise this would have worked, but thank you for the help.

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

    Re: Date formula that functions like WORKDAY but includes weekends

    Quote Originally Posted by Whizbang View Post
    =C2+D2+SUMPRODUCT(--(C2<$A$2:$A$4),--(C2+D2>$A$2:$A$4))
    I don't think this will work in all circumstances. In the example with 60 days for example, adding another holiday, e.g. on 15/04/2011 should make the end date move back 1.

    I believe you need an array formula like this

    =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 holidays is a named range containing holiday dates
    Audere est facere

  7. #7
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Date formula that functions like WORKDAY but includes weekends

    Hi

    With a defined range to include any holiday dates, wouldn't the following achieve what you want.

    =A1+5+COUNTIF(Holidays,A1+5)
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

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

    Re: Date formula that functions like WORKDAY but includes weekends

    I'm not sure how that's supposed to work, Roger.

    If A1 has 23rd December 2011 and 25th and 26th December 2011 are included in the holiday range then shouldn't the result be 30th December 2011? Your formula will return just A1+5 = 28th December 2011. My suggested array formula will return 30th Dec as expected

    I note also that 1gambit says he is using Excel 2010 (although 2003 is shown in profile) so with WORKDAY.INTL function from 2010 should be able to use

    =WORKDAY.INTL(C2,D2,"0000000",holidays)

    [untested]

  9. #9
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Date formula that functions like WORKDAY but includes weekends

    Hi

    Quite right, ddl, works fine with Intl version of Workday.
    My proposal was only testing if 5 days after the date was a holiday.

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

    Re: Date formula that functions like WORKDAY but includes weekends

    yes this formula works.
    Is there any method to exclude only Sundays (without listing the sundays in holidays name range). I use Excel 2007 and therefore workday.intl is not available. Please also tell that whether any add-in available for workday.intl usage in excel 2007.

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

    Re: Date formula that functions like WORKDAY but includes weekends

    This version adds workdays excluding Sundays only

    =A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

    assuming start date in A1 and days to add in B1

    That doesn't exclude holidays, though, do you need that?

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

    Re: Date formula that functions like WORKDAY but includes weekends

    Yes i need to exclude sundays and holidays both

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

    Re: Date formula that functions like WORKDAY but includes weekends

    Yes please I need to exclude both holidays and sundays

    Quote Originally Posted by daddylonglegs View Post
    This version adds workdays excluding Sundays only

    =A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

    assuming start date in A1 and days to add in B1

    That doesn't exclude holidays, though, do you need that?

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

    Re: Date formula that functions like WORKDAY but includes weekends

    anybody please.

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

    Re: Date formula that functions like WORKDAY but includes weekends

    Please help
    TIA

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

    Re: Date formula that functions like WORKDAY but includes weekends

    you can use this, where F contains yoru holidays, and G contains a list of 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.

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

    Re: Date formula that functions like WORKDAY but includes weekends

    Thanks
    For this one has to write all sundays in G:G to work this. But if i had to do this i can achieve it with the formula in post #6 of this thread, by including all sundays in 'holidays' itself.
    Last edited by anilrini; 03-02-2012 at 02:54 AM.

  18. #18
    Registered User
    Join Date
    02-08-2014
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Date formula that functions like WORKDAY but includes weekends

    thanks Wizbang, you're a genius!

  19. #19
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    839

    Re: Date formula that functions like WORKDAY but includes weekends

    I know I am bumping an old post (3+ years), but in searching I am trying to do similar here as well

    Is it possible to compute similar to the WorkDay function but include all weekends, both Saturday and Sundays, but no holidays?

+ 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