+ Reply to Thread
Results 1 to 5 of 5

Count workday and produce result.

  1. #1
    Registered User
    Join Date
    06-28-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    10

    Count workday and produce result.

    Hi All

    We have monthly client deliverable that need to be delivered on certain workdays of the month.

    I need help with a formula that can tell me what date the 4th workday of the month is. Is there also a way of taking into account bank holidays?

    Thanks
    Colin

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Count workday and produce result.

    yes, you can use workday

    =WORKDAY(DATE(2022,1,1)-1,4,Z1:Z10)
    where Z1:Z10 hold PH dates

    so if Z1 held 3rd Jan, the above would return Fri 7th Jan as 4th workday.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count workday and produce result.

    Try this:

    =WORKDAY.INTL(EOMONTH(TODAY(),-1)+1,4,1,I2:I8)

    The range at the end is where you list your bank holidays (it can be any range in your workbook). The formula excludes weekends and works for the current month.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    06-28-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    10

    Re: Count workday and produce result.

    XLent

    I managed to get this to work. Thank you so much.

    Now to be a pain... Is there an option to do the same but go back to the previous workday?

    Thank you again.

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Count workday and produce result.

    when you say previous workday -- from what, exactly?

    If you mean, you want WD3 rather than WD4, you can either,

    a) use the exact same function as for WD4 but change the reference to 4, to 3
    or
    b) use the result of the 4th workday calc, as the start date, but use -1 as the workday requirement (rather than 4)

    if you mean you want WD-1 then

    =WORKDAY(DATE(2022,1,1),-1,Z1:Z10)

    of course, you would want to ensure your holiday range (Z1:Z10 in example) contains any/all holidays that might be relevant to the calculation (say 2021-2)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Getting ISBLANK to show either blank field or WORKDAY result
    By Landerolin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-29-2018, 07:38 AM
  2. [SOLVED] produce text result instead of #NUM! result in array formula
    By Stuartzz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2018, 08:17 PM
  3. [SOLVED] If Formula To Produce Word Result
    By Gtrtim112 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-16-2015, 10:30 PM
  4. combine three macros to produce the result
    By RobinPrice in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-06-2014, 12:38 AM
  5. Excel 2007 : Compare 2 columns and produce 1 result
    By fentontech in forum Excel General
    Replies: 3
    Last Post: 11-04-2011, 02:26 PM
  6. Produce a result from a drop down list
    By Emz1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2009, 02:20 AM
  7. Convert to CSV produce different result
    By salwani in forum Excel General
    Replies: 1
    Last Post: 07-28-2008, 08:23 AM

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