Date issue to automate calendar

[Babs in Ohio]

I have a start date of 11/1/2008 - What I want to populate is a table that
has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with
December.

I know I can type the column headers, but I want the column headers to
display the correct year if my start date becomes 2/1/2009. Therefore Jan-09,
Feb-09, Mar-09 etc.

Is there any formula that I can use?

 

[Chip Pearson]

You can use the following formula.

=DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1)

This assumes that your start date is in cell A1 and this formula is place in
F1 and filled to the right out to column P, which will list December. Change
the reference to $F1 to the first cell in which the formula is entered. Use
the $ character as shown.

 

[Babs in Ohio]

Chip,

Thank you for your reply.

I put 11/1/2008 in cell A1 and put your formula in F1. What I got is:
cell F1 = 11/1/2008
cell F2 = 12/1/2008
cell F3 = 1/1/2009

Am I missing something. Did I change your formula?

 

[Dave Thomas]

Put the following in F1 and drag the formula thru Q1
=DATE(YEAR($A1),MONTH(1)+COLUMN()-COLUMN($F1),1)
Then format cells F1 thru Q1 with a custom format of mmm-yy. Then enter your
date in A1.
If you start the formula in some other column than F, say column C, change
the $F in the formula to $C.

 

[Peo Sjoblom]

I assume you meant F1, G1 and so on?

What did you expect to get?

If you meant that you didn't get Jan-08, Feb-08 that is because you cannot
get a date in Excel with just month and year, you need the day as well.
However if you just format F1 as

mmm-yy then copy the formula to the right you will get what you want


Note that 11/01/08 in the US is November the first, not that it matter since
all the formula does is to take the date in A1 and change it to the first of
that particular month and year



--
Regards,

Peo Sjoblom

Chip,

Thank you for your reply.

I put 11/1/2008 in cell A1 and put your formula in F1. What I got is:
cell F1 = 11/1/2008
cell F2 = 12/1/2008
cell F3 = 1/1/2009

Am I missing something. Did I change your formula?

 

[Peo Sjoblom]

Or even

=DATE(YEAR($A1),COLUMNS($A$1:A1),1)

 

[Dave Thomas]

I was merely removing the MONTH function, yet keep it simple for people to
understand. We can obfuscate these things all we want and make them so
obtuse, the beginner doesn't have a clue.

Or even

=DATE(YEAR($A1),COLUMNS($A$1:A1),1)

 

[Peo Sjoblom]

Are you saying that it easier to understand

1+COLUMN()-COLUMN($F1)

vis-à-vis

COLUMNS($A$1:A1)


me thinks not, I believe both are confusing with regards to a date increment

OTOH my formula can be put in any cell whereas yours will return the
incorrect result if put anywhere but the F column

 

[Peo Sjoblom]

If you always want the first month of the particular year in A1 use

=DATE(YEAR($A1),COLUMNS($A$1:A1),1)

format as mmm-yy and copy across

 

[Dave Thomas]

I was merely correcting the formula that Chip submitted. Babs wanted to know
why it didn't work. I was just showing her with the original formula.
Changing the formula completely does not demonstrate why the original does
not work. Your formula is easy for me to understand but it is not obvious to
beginners.

 

[Rick Rothstein \(MVP - VB\)]

Or even

=DATE(YEAR($A1),COLUMNS($A$1:A1),1)

This further reduction seems to work also...

=DATE(YEAR($A1),COLUMN(A1),1)

Rick

 

[Rick Rothstein \(MVP - VB\)]

Maybe I am missing something, but Chip's formula appears to work fine for me
as long as it is placed in column F (and once you format the cells to show
mmm-yy, of course).

Rick

 

[Peo Sjoblom]

Won't work if you insert a column, that is the whole point of using columns
vs column

 

[Chip Pearson]

Nice, but that is not an answer to the question which is why does Chips

formula not work.

My formula did work as I intended, but Babs entered in down rows in a single
column, not across columns in a single row. But as others have shown, mine
was not the simplest answer.I think Rick wins the prize for simplicity.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rick Rothstein \(MVP - VB\)]

Or even

Won't work if you insert a column, that is the whole point of using
columns vs column

Yes, that is true... but one must wonder how often it will be necessary to
protect against the insertion of a new column into a series of 12 columns
that span January to December (I would think any such situations would be
very rare indeed).

Rick

 

[Peo Sjoblom]

True


--
Regards,

Peo Sjoblom

Yes, that is true... but one must wonder how often it will be necessary to
protect against the insertion of a new column into a series of 12 columns
that span January to December (I would think any such situations would be
very rare indeed).

Rick

 

[Dave Thomas]

Your formula did not give what Babs wanted. She wanted the dates to run from
Jan to Dec for any input date. Your formula is based on the month of the
input date. Look carefully at her original message:

I have a start date of 11/1/2008 - What I want to populate is a table that
has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with
December.

I know I can type the column headers, but I want the column headers to
display the correct year if my start date becomes 2/1/2009. Therefore
Jan-09,
Feb-09, Mar-09 etc.

Your formula =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) depends on
the month in the date.

Your formula should have been =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) to
give her what she wanted.

Regards

Dave

 

[Dave Thomas]

Chips's formula did not give what Babs wanted. She wanted the dates to run
from
Jan to Dec for any input date. Chips' formula is based on the month of the
input date. Look carefully at her original message:

I have a start date of 11/1/2008 - What I want to populate is a table that
has columns for Jan-08, Feb-08, Mar-08 etc begining with January ending with
December.

I know I can type the column headers, but I want the column headers to
display the correct year if my start date becomes 2/1/2009. Therefore
Jan-09,
Feb-09, Mar-09 etc.

Chips' formula =DATE(YEAR($A1),MONTH($A1)+COLUMN()-COLUMN($F1),1) depends on
the month in the date.

Chips' should have been =DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1) to
give her what she wanted.

Regards

Dave

 

[Rick Rothstein \(MVP - VB\)]

Ah! Yes, but of course.

<LOL>That's what I get for testing it with a starting date in January!</LOL>

Rick

 

[Dave Thomas]

Never have I seen so much ado about nothing. hehehe