Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    custom series (2003 sp3)

    I need to create a column of dates where each date represents the 3rd Wednesday of that month. I entered the first 6 dates and then attempted to create a custom series from that list but cannot get it to work. i am obviously missing something in following the steps outlined in the help file. Is there another way I can do this?

    I also attempted to create the list by typing it into the NEWLIST box and failed at that as well.
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: custom series (2003 sp3)

    Since the 3rd Wednesday of a month doesn't form a "regular" series, you'll have to use formulas. Try this; I've used columns A and B in the example, and assumed that you want to start in January, 2009.
    - Enter 01/01/2009 in A2 and 02/01/2009 in A3 (US date format).
    - Select A2:A3 and fill down as far as needed.
    - Enter the following formula in B2:

    =A2+MOD(4-WEEKDAY(A2),7)+14

    - Fill down as far as needed.

    =A2+MOD(4-WEEKDAY(A2),7) is the first Wednesday of the month (The 4 stands for Wednesday, 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday), and +14 gives the third Wednesday.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: custom series (2003 sp3)

    Thank you Hans. Once again after I read the help for MOD and WEEKDAY, I must acknowledge the Lounge really provides a great service to us would-be Excel users.

    And I take it a custom series must be "regular".
    Thanks
    chuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: custom series (2003 sp3)

    A custom list can be anything you want, but it doesn't know how to extend itself. So you'd have to enter ALL dates you need.

Posting Permissions

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