help with dates please

[dido22]

Can someone please help me generate a list of dates in a column. I just need
month and year, so Jan 2010, Feb 2010, Mar 2010 .....etc. No days or dates.
Thanks KK

 

[joeu2004]

Can someone please help me generate a list of dates in a
column. I just need month and year, so Jan 2010, Feb 2010,
Mar 2010 .....etc. No days or dates.

The simplest way is to enter a full date, for example 1/1/2010, then
format as Custom "mmm yyyy" without quotes. That only makes it easier
to write formulas that might depend on that date.

As for propagating down the column, that depends on how many dates you
have to enter. The following are 2 ways.

1. Enter 1/1/2010 into A1 and 2/1/2010 into A2 (expressed in m/d/yyyy
form; use the form consistent with your region).
Select A1 and A2.
Drag the lower-right corner down the column.

2. Enter 1/1/2010 into A1.
Click on Edit > Fill > Series.
Select Columns, Date, Month.
In Stop Value, enter the last date, e.g. 1/1/2012.
Click OK

 

[joeu2004]

As for propagating down the column, that depends on how
many dates you have to enter.  The following are 2 ways.

I forgot to mention a 3rd way, albeit not as easy.

3. Enter 1/1/2010 into A1. Enter =EDATE(A1,1) into A2.
Alternatively, enter =DATE(YEAR(A1),1+MONTH(A1),1) into A2.
Copy A2.
Paste into the reminder of the column, either by selecting the
intended range starting with A3, or by entering the remaining range
into the Name Box, e.g. A3:A100.

In XL2003 or earlier, if you used EDATE and get a #NAME error, simply
use the DATE alternative or go to the EDATE help page for instructions
regarding the ATP.

 

[joeu2004]

Errata (typo)....

The simplest way is to enter a full date, for example
1/1/2010, then format as Custom "mmm yyyy" without quotes.
 That only makes it easier to write formulas that might
depend on that date.

"That only" should be "that also".

 

[Cimjet]

Hi Dido22
First highlight the column you want those dates, then do a custom formatting
like this mmm/yyyy.
Paste this formula in the top cell and drag down. =DATE(2010,1+ROW(A1),0)
My sample will start with Jan.2010, you just change the year and the number just
after the year for the month and copy down.
HTH
John

 

[Dave Peterson]

You could use something like this in A1:
=date(2010,row(),1)

=row() will return 1 if the formula is in row 1. Adjust it to start where you like:
=date(2010,row()-12,1)
If you start in row 13.

Then copy down (and convert to values????).

Then give the range a nice number format: mmmm yyyy

It'll still be the first of each month. You'll see it in the formula bar, but
you won't see it in the cell.