+ Reply to Thread
Results 1 to 4 of 4

Workday function for 7 day work week (only observing holidays)

  1. #1
    Registered User
    Join Date
    04-16-2010
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Workday function for 7 day work week (only observing holidays)

    Hi all,

    I have been trying to tackle this from several different angles with no luck. I am trying to use the workday function or something similar to calculate a date after a number of working days say 100. The workday function however, and even the workday.intl forces you to choose at least one weekend day. I would like to do this with holidays being the only days off and every other day being a working day. Would appreciate any feedback.

    Thanks,

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

    Re: Workday function for 7 day work week (only observing holidays)

    You can specify that all days are working days with WORKDAY.INTL, using "0000000" as the weekend days string, e.g.

    =WORKDAY.INTL(A2,100,"0000000",H$2:H$100)

    that adds 100 working days to A2 excluding only the holidays specified in H2:H100 [untested]
    Last edited by daddylonglegs; 05-10-2012 at 04:00 PM. Reason: changed WORKDAY to WORKDAY.INTL
    Audere est facere

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

    Re: Workday function for 7 day work week (only observing holidays)

    .....or this "array formula" should give the same result in earlier versions

    =SMALL(IF(COUNTIF(H$2:H$100,ROW(INDIRECT("1:"&B2*10))+A2)=0,ROW(INDIRECT("1:"&B2*10))),B2)+A2

    confirmed with CTRL+SHIFT+ENTER

    A2 start date, B2 days to add and H2:H100 holiday range

  4. #4
    Registered User
    Join Date
    04-16-2010
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Workday function for 7 day work week (only observing holidays)

    Worked perfectly. Thank you very much.

+ 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