Creating month sequences

  • Thread starter Thread starter Henry Stock
  • Start date Start date
H

Henry Stock

I was wondering...

Is there an easy way to create a range of cells that a) show just the month
and year and b) can calculate the next month and year for each cell in the
range?

Example:
jan-06 feb-06 ... nov-10 dec-10
 
Enter the first 2 dates (I use the m/d/y format):

A1 = 1/1/2006
B1 = 2/1/2006

Select both A1 and B1 and format the way you want:

Format>Cells>Custom
mmm-yy
OK

With both cells still selected "grab" the fill handle and drag across as
needed.
 
Hi Henry,

Here are a number of ways:

1. Enter a date such as 1/1/06 and format it to show Jan-06
2. Drag the fill handle and when you release it open the Auto Fill Options
icon at the bottom right corner of the last cell and choose Fill Months.

1. Do the same as step 1 above.
2. Drag the fill handle with the RIGHT mouse button and when you release it
choose Fill Months from the popup menu.

1. Do the save as step 1 above, say cell A1
2. In cell B1 enter the formula =EDATE(A1,1) and copy this formula to the
right as far as necessary.
(In 2003 and earlier you must attach the Analysis ToolPak add-in to access
this function.)

1. Repeat step 1 above
2. In cell B1 enter the formula =EOMONTH(A1,1) and copy as above (again
requiring the Analysis ToolPak.

1. Repeat step 1 above
2. In cell B1 enter the formula =DATE(YEAR(A2),MONTH(A2)+1,1) this formula
does not require the analysis toolpak.

Cheers,
Shane Devenshire
Microsoft Excel MVP
 
Back
Top