+ Reply to Thread
Results 1 to 4 of 4

How can I calculate workday date when only have calendar days?

  1. #1
    Registered User
    Join Date
    03-23-2011
    Location
    West Bromwich, England
    MS-Off Ver
    Excel 2007 & 2013 in Azure
    Posts
    83

    How can I calculate workday date when only have calendar days?

    Hi All,

    From a Pay date, how can I calculate a claim date when I only have calendar days.

    Based on the following:

    Pay Date = Mon 08/05/2017
    Claim date = less 7 calendar days from Pay Date
    Bank Holiday = Mon 01/05/2017
    Rule 1 = The claim date must be a workday (Mon-Fri) excl bank holidays.
    Rule 2 = Only if the initial calculation for the claim date falls on a weekend day or bank holiday, then we need to take the first available date beforehand. Weekend days or bank holidays do not otherwise affect the calculation.

    I would expect the claim date to be Fri 28/04/2017.

    Due to a combination of only having calendar days AND the 2 rules, I can't see how I can combine the WORKDAY & NETWORKDAYS functions to get the correct results. Does anyone have any idea how to achieve this?

    Thanks

    Tooley

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,714

    Re: How can I calculate workday date when only have calendar days?

    Try

    =WORKDAY.INTL(A1,-5,"0000011",F1)

    A1=Pay Day

    F1=Holiday (01/05/2017)

    For future use, create a named range ("Holidays") and replace F1 with Holidays

    WORKDAY.INTL(A1,-5,"0000011",Holidays)

  3. #3
    Registered User
    Join Date
    03-23-2011
    Location
    West Bromwich, England
    MS-Off Ver
    Excel 2007 & 2013 in Azure
    Posts
    83

    Re: How can I calculate workday date when only have calendar days?

    Hi John,

    I've not used the INTL version and was just looking it up, many thanks for the help. I should have informed the calendar days is flexible so I cannot manually code, however the following seems to work on the example:

    A1 = Pay Day
    A2 = Calendar Days
    Holidays = named range for bank hols

    WORKDAY.INTL(A1,-NETWORKDAYS(A1-A2,A1)+1,"0000011",Holidays)

    Trying to solve the larger issue, I made a basic mistake in that NETWORKDAYS counts total weekdays between dates (not the difference) I forgot to adjust for this, so the deduction in WORKDAY calculates correctly.

    Thanks again for your help
    Last edited by Tooley; 05-03-2017 at 09:30 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,714

    Re: How can I calculate workday date when only have calendar days?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Replies: 3
    Last Post: 12-09-2014, 09:29 PM
  2. VBA to Calculate Start Date from End Date and Dev Time Using WORKDAY()
    By excelforumkeys in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2014, 05:06 PM
  3. [SOLVED] Calculate calendar days
    By Aland2929 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-17-2014, 09:32 AM
  4. Replies: 3
    Last Post: 10-30-2013, 05:15 AM
  5. [SOLVED] Calculate Calendar days
    By Aland2929 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-30-2013, 04:18 AM
  6. [SOLVED] workday function --> calculate START date
    By kaaver in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2010, 11:31 AM
  7. Replies: 8
    Last Post: 05-16-2009, 03:49 AM

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