custom series (2003 sp3)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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”.

Reply | Quote

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.

Reply | Quote