Fill Series by Date Won't Work

[Lolamoth]

Version: v.X
Operating System: Mac OS X 10.3 (Panther)
Processor: Power PC

My group meets the third Wednesday of every month. I'd like to create a list of future meeeting dates using the fill series function. In the first cell (formatted M/D/Y) I enter the date of the this month's meeting (4/16/08). In the cell directly under that, I enter next month's meeting date (5/21/08). I select both, then press Control while dragging the handle. No matter what I choose from the popup contextual menu (Day, Weekday, whatever) I can't get the series to fill projected dates for the rest of the year. Help!

 

[Laroche J]

Version: v.X
Operating System: Mac OS X 10.3 (Panther)
Processor: Power PC

My group meets the third Wednesday of every month. I'd like to create a list
of future meeeting dates using the fill series function. In the first cell
(formatted M/D/Y) I enter the date of the this month's meeting (4/16/08). In
the cell directly under that, I enter next month's meeting date (5/21/08). I
select both, then press Control while dragging the handle. No matter what I
choose from the popup contextual menu (Day, Weekday, whatever) I can't get the
series to fill projected dates for the rest of the year. Help!

Assuming the first date is in cell A1, insert this formula in cell A2 and
copy down.
=DATE(YEAR(A1);MONTH(A1)+1;22-WEEKDAY(DATE(YEAR(A1);MONTH(A1)+1;4)))

For other days of the week than Wednesday, replace the last 4 in the formula
with:
Thursday: 3
Friday: 2
Saturday: 1
Sunday: 7
Monday: 6
Tuesday: 5


JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[suzanne]

Thanks for the formula. I must not be doing some important step because still no results.

Here's what I did:

Formatted all cells in the worksheet to Date style 0/00/00.

Typed this month's meeting date in cell A1: 4/16/08

Clicked on A2, then chose Insert > Function. I couldn't paste until I'd chosen a function, so I chose Date & Time > DATE, which inserts "=Date()" into the formula window.

I clicked inside the () and was presented with a dialog with three separate date windows. Didn't know what to put in there—never have understood that damn box. Clicked out of it. Ignored the error message.

I pasted the formula into the (). The result was:

=DATE(=DATE((YEAR(A1);MONTH(A1)+1;22-WEEKDAY(DATE(YEAR(A1);MONTH(A1)+1;4)))))

Of course with the duplicate "=DATE" I got an error. I removed one of them but still got an error. I removed extra parens ")" at the end, one at a time. No luck.

The last step you mentioned was "copy down." Does that mean drag the fill handle?

Sorry to be so thick. I’m not new to Excel but I’m not too savvy about functions.

 

[Laroche J]

suzanne wrote on 2008-04-17 10:02:

Thanks for the formula. I must not be doing some important step because still
no results.

Here's what I did:

Formatted all cells in the worksheet to Date style 0/00/00.

Typed this month's meeting date in cell A1: 4/16/08

Clicked on A2, then chose Insert > Function. I couldn't paste until I'd chosen
a function, so I chose Date & Time > DATE, which inserts "=Date()" into the
formula window.

I clicked inside the () and was presented with a dialog with three separate
date windows. Didn't know what to put in there—never have understood that damn
box. Clicked out of it. Ignored the error message.

I pasted the formula into the (). The result was:

=DATE(=DATE((YEAR(A1);MONTH(A1)+1;22-WEEKDAY(DATE(YEAR(A1);MONTH(A1)+1;4)))))

Of course with the duplicate "=DATE" I got an error. I removed one of them but
still got an error. I removed extra parens ")" at the end, one at a time. No
luck.

The last step you mentioned was "copy down." Does that mean drag the fill
handle?

Sorry to be so thick. I’m not new to Excel but I’m not too savvy about
functions.

First I have to apologize, the formula should have been
=DATE(YEAR(A1),MONTH(A1)+1,22-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,4)))
with commas instead of semi-colons between arguments (my OS X is set to
French, hence the difference).

Secondly, to insert the formula in your workbook, proceed this way.
Select it her in the message (the whole line), and Copy.
Click on the cell where you need the formula (probably the cell below the
first date), and Paste.
If you first date is not in A1, replace all A1 with the correct cell
reference.
Copy down, yes with the fill handle.

Hope this helps.

JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[Suzanne]

Thank you for your detailed and helpful instructions! I'm sorry, but it didn't work. Here's what I did:

1. Selected all cells and formatted them 0/00/00.

2. Selected A1 and inserted 6/19/08.

3. Selected your formula
=DATE(YEAR(A1),MONTH(A1)+1,22-WEEKDAY(DATE(YEAR(A1),MONTH(A1)
and chose Copy.

4. Selected A2 and chose Paste.

The formula remained in A2 as a text string. Was there something I missed?

Thanks,
S :worried: :worried:

 

[Suzanne]

Realizing you probably meant to insert the formula into the Formula Bar, I pasted it there and got a message the formula had an error. I'd appreciate any ideas you might have about this.

Thanks,
S

 

[JE McGimpsey]

Try:

A1: 6/18/2008
A2: =DATE(YEAR(A1),MONTH(A1)+1, 1+((3 - (4 >=
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))))*7) +
(4 - WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1))))


Where the 4 corresponds to Wednesday (Sunday = 1) and the 3 means the
3rd Wednesday of the month.