Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date series excluding weekends AND holidays (XP)

    Hi again everyone - I know there's an easy way to do this - can't remember what it is. I want to fill a series of dates, excluding weekdays, and specific holiday dates I've put in a column.

    Thanks again!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date series excluding weekends AND holidays (XP)

    I believe that the WORKDAY function in the Analysis Toolpak Addin will do what you want. See the description below from the Excel Help file. If you don't have the Analysis Toolpak installed (it's on the Office CD), you must install it before using this function.

    <hr>
    WORKDAY
    See Also

    Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. To view the number as a date, click Cells on the Format menu, click Date in the Category box, and then click a date format in the Type box.

    If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

    How?

    Syntax

    WORKDAY(start_date,days,holidays)

    Start_date is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998"))

    Days is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

    Holidays is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates. Learn about array constants. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.

    Remarks

    Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. Learn more about how Microsoft Excel stores dates and times.


    If start_date is not a valid date, WORKDAY returns the #NUM! error value.


    If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.


    If days is not an integer, it is truncated.

    Examples

    WORKDAY(DATEVALUE("01/03/1998"),5) equals 35804 or January 9, 1998.

    If January 7, 1998, and January 8, 1998, are holidays, then:
    WORKDAY(DATEVALUE("01/03/1998"),5,{35802,35803}) equals 35808 or January 13, 1998.

    <hr>
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date series excluding weekends AND holidays (X

    Nope - that's not what I need. I need

    July 1, 2004 July 2, 2002 July 6, 2004
    Thurs Fri Tue

    Skipped Saturday, Sunday and Monday which is July 4th

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date series excluding weekends AND holidays (X

    I think that is exactly what you need, Put this in cell A1:

    <pre>=WORKDAY(StartDate,ROW(),HolidayList)</pre>

    Where startdate is the date 1 day before you start and HolidayList is a range that contains a list of the holidays to exclude.
    for example:
    <pre>=WORKDAY("1/1/2004",ROW(),$C$1:$C$20)</pre>


    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date series excluding weekends AND holidays (X

    That will certainly give that to you. Assume that your holiday list is in C1:C10, and you want your dates in A1:A365. Then put the starting date (7/1/2004) in cell A1. Then put the following formula in cell A2:

    <pre>=WORKDAY(A1,1,$C$1:$C$10)
    </pre>


    Now copy this formula down to A365. I assume that your series is missing 7/5/2004 because it is in your holiday list.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date series excluding weekends AND holidays (X

    Steve,

    Assuming that the first formula cell (that is, the first cell where the formula will be entered) is A2...

    =WORKDAY(StartDate,ROW()-ROW($A$2)+1,HolidayList)

    would be robust against user actions like iinserting one or more rows before the formula row.

    Aladin
    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date series excluding weekends AND holidays (X

    You are correct.
    I assumed startdate (as I mentioned) was 1 day less then date you wanted first. I also assumed (though never mentioned) that the series would be filled and then the values pasted. I would see no reason to want to keep the calcs once the series was filled.

    Personally, I like Legare's formula better than mine.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date series excluding weekends AND holidays (X

    Sorry for the doubting Thomas stuff - you are 100% correct, as always.

    Thank you.

    Aunt Linda

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date series excluding weekends AND holidays (X

    Works just fine - thanks.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date series excluding weekends AND holidays (X

    Your assumptions were correct - and I tend to agree with your assessment of Legare's formula.

    Thanks for the replies - I really appreciate all this help.

    Aunt Linda

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •