+ Reply to Thread
Results 1 to 7 of 7

Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Exclamation Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

    i want to create a list that will allow me to put a date on A1 and the A2,A3,A4,A5,A6 Ect... show the next date. I use the Formula =A1+1 to give the next date. So i would like to do that but have it when the date falls on a Sat or Sun it will skip it and go to the Monday. or if a date fall on a Holiday (which i created a Separate Tab to make a chart of holidays im using. Columns A1-Date & A2-Description in the Chart) that it will skip it and go to the next week day.

    For Example December 2014 Christmas falls on the 24th and 25th for days off for work and the weekend is the 20th & 21st so i want it to automatically do this.

    Friday 12/19/14 (i will type this date im myself)
    Monday 12/22/14 (This will run of the Previous date automatically)
    Tuesday 12/23/14 (This will run of the Previous date automatically)
    Friday 12/26/14 (This will run of the Previous date automatically)

    Is this Possible

  2. #2
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

    Check this I hope this will help u


    Bok2.xlsx
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

    Thanks but I`m not understanding it. Because every i add a new Holiday date or even a new date all it gives me is #value.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

    Try this...

    Data Range
    A
    B
    C
    D
    1
    Date
    Weekday
    Holidays
    2
    12/19/2014
    Fri
    1/1/2014
    3
    12/22/2014
    Mon
    7/4/2014
    4
    12/23/2014
    Tue
    12/24/2014
    5
    12/26/2014
    Fri
    12/25/2014
    6
    12/29/2014
    Mon
    7
    12/30/2014
    Tue
    8
    12/31/2014
    Wed

    A2 = user entered date

    Enter this formula in A3 and copy down as needed:

    =WORKDAY(A2,1,D$2:D$5)

    Enter this formula in B2 and copy down as needed:

    =TEXT(A2,"ddd")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

    can u send me that file so i look that
    i think the problem is date formatting, U entered mm/dd/yyyy format and that sheet has dd/mm/yyyy format (I m not sure about that) so please send me that file which showing #value error

  6. #6
    Registered User
    Join Date
    09-12-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

    I just wanted to say thank you guys for helping me out. and thank you pwnyadav007 but Tony Valko showed me a way that worked. But i just wanted to say thanks for everyone input. Have a good Day.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need to create a List of Dates that will Exclude Weekends and Holidays Automatic

    You're welcome. We appreciate the feedback!

+ 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. VBA - List Dates excluding Weekends and Holidays
    By tykhoo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2012, 11:13 PM
  2. Replies: 4
    Last Post: 03-08-2012, 02:57 PM
  3. Replies: 10
    Last Post: 11-29-2011, 08:21 PM
  4. String of dates to exclude weekends AND holidays
    By FEI7774 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-01-2009, 06:19 PM
  5. Finding weekends and holidays in a list of dates
    By mat13wat in forum Excel General
    Replies: 2
    Last Post: 08-04-2007, 07:02 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