Date issue to automate calendar

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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?
 
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.
 
Essentially the same formula but more elegant,
=DATE(YEAR($A1),1+COLUMN()-COLUMN($F1),1)
 
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






Babs in Ohio said:
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?
Click to expand...
 
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.

Peo Sjoblom said:
Or even

=DATE(YEAR($A1),COLUMNS($A$1:A1),1)
Click to expand...
 
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
 
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
 
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.
 
Nice, but that is not an answer to the question which is why does Chips
formula not work.
 
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
 
Nice, but that is not an answer to the question which is why does Chips
formula not work.
Click to expand...

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)
 
Or even
Won't work if you insert a column, that is the whole point of using
columns vs column
Click to expand...

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
 
True


--
Regards,

Peo Sjoblom



Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
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
 
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
 
Back
Top