+ Reply to Thread
Results 1 to 3 of 3

Working days and holidays between dates/ranges

Hybrid View

  1. 02-10-2014, 10:18 AM #1
    marielouise1
    marielouise1 is offline
    Registered User
    Join Date
    09-03-2013
    Location
    Belfast, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    18

    Working days and holidays between dates/ranges

    Hey

    I am trying to work with working days and holidays for 3 people.
    For column I (Days from initial to reviewed), I am trying to calculate this:

    Number (positive integer) of days between initial date and reviewed date taking into account weekends and holidays.
    Conditions:
    IF Initial = Reviewed, display zero days.
    IF column D says 'D MacKenzie' - use dealist, IF 'S Agnew' use stevelist, IF 'I McIntosh' use ianlist.

    I have this formula inputted to calculate due date (2 working days after initial) which accounts for holidays:
    =IF(D12="D Mackenzie",WORKDAY(B12,2,deanlist))+IF(D12="S Agnew",WORKDAY(B12,2,stevelist))+IF(D12="I McIntosh",WORKDAY(B12,2,ianlist))


    Thank you,
    Marie-Louise

    Data Trial - SAMPLE.xlsx
    Reply With QuoteRegister To Reply
  2. 02-10-2014, 11:07 AM #2
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Working days and holidays between dates/ranges

    Hi

    formulas in column I are equivalent to

    =IF(G12=A12,0,G12-B12-C12)


    Using

    =IF(G12=A12,0,NETWORKDAYS(B12,G12,IF(D12="D Mackenzie",deanlist,IF(D12="S Agnew",stevelist,IF(D12="I McIntosh",ianlist)))))

    I'm comparing B12 (initial date) and G12 (date reviewed) escluding saturdays sundays and relevant holidays ( I hope)

    Please see sample file attached.

    Hope it's a little help
    Attached Files Attached Files
    Last edited by canapone; 02-10-2014 at 11:11 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  3. 02-10-2014, 11:15 AM #3
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Working days and holidays between dates/ranges

    Hi again,

    if I've undestood your problem it would be better to change first segment of the formula with

    =IF(G12<=B12


    =IF(G12<=B12,0,NETWORKDAYS(B12,G12,IF(D12="D Mackenzie",deanlist,IF(D12="S Agnew",stevelist,IF(D12="I McIntosh",ianlist)))))

    Please consider it just an attempt.



    Regards
    Last edited by canapone; 02-10-2014 at 11:17 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate Working Days (excluding weekends & holidays) between two dates/times
    By bool in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-19-2013, 05:45 AM
  2. Number of days between two dates without Holidays
    By kmcbriarty in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-10-2013, 03:00 PM
  3. [SOLVED] Calculate working days excluding holidays from Jan 11 to Dec 13
    By Cortlyn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2013, 09:46 AM
  4. Excel 2003 - Calculate working days and holidays
    By meckenzie2012 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2013, 02:12 PM
  5. Excel 2007 : Working Hrs, Days, Weekends, Holidays calculator
    By Bazzzer in forum Excel General
    Replies: 8
    Last Post: 09-01-2010, 11:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1