Month and Year calc

  • Thread starter Thread starter Cas
  • Start date Start date
C

Cas

Hi all

Could someone help me with a formula to do the following :

I wish to enter a date in cell A1 in the format Jan-04 (mmm-yy) - that's the
easy bit !

I then want excel to populate subsequent cells B1, C1.....etc with Feb-04,
Mar-04.....etc.

If I then change A1 to say Aug-04 I would like subsequent cells to follow
the sequence Sep-04, Oct-04 etc. The worksheet will have to cover 5 years
so obviously Jan-05 will have to follow Dec-04 and so on.

TIA

Cas
 
Hi
if you enter in cell A1 a date (and format it with your custom format
MMMM-YY) try the following functuon in B1
=DATE(YEAR(A1),MONTH(A1)+1,1)
and format this cell also with your custom format
 
1. Select B2:BH2.
2. Type in
=DATE(YEAR(A1),MONTH(A1)+1,1)
3. Press <Ctrl><Enter>.

BTW - Entering "Jan-04" directly into a cell actually
returns 1/4/04. Better to enter 1/1/04 and format as mmm-
yy.

HTH
Jason
Atlanta, GA
 
Works a treat - Many thanks Frank.

Frank Kabel said:
Hi
if you enter in cell A1 a date (and format it with your custom format
MMMM-YY) try the following functuon in B1
=DATE(YEAR(A1),MONTH(A1)+1,1)
and format this cell also with your custom format
Click to expand...
 
Thanks Jason - this also worked fine.

Jason Morin said:
1. Select B2:BH2.
2. Type in
=DATE(YEAR(A1),MONTH(A1)+1,1)
3. Press <Ctrl><Enter>.

BTW - Entering "Jan-04" directly into a cell actually
returns 1/4/04. Better to enter 1/1/04 and format as mmm-
yy.

HTH
Jason
Atlanta, GA
Click to expand...
 
Cas

one way:

If the start date is in cell A1, put the following formula in B1 and drag
across as far as you need to go

=IF(ISERROR(DATEVALUE("01/"&MONTH(A1)+1&"/"&YEAR(A1))),DATEVALUE("01/01"&"/"
&YEAR(A1)+1),DATEVALUE("01/"&MONTH(A1)+1&"/"&YEAR(A1)))

Regards

Trevor
 
Hells Bells Trevor - that's what I call a formula !!

Are there any benefits/drawbacks in using this as opposed to a previous
posters method.
=DATE(YEAR(A1),MONTH(A1)+1,1)

I assume that you cut & pasted this so many thanks for that.
However if you typed it - many many many many thanks !!

Cas
 
Cas

no benefits whatsoever. It just scores highly on artistic merit ...
balanced by lack of style !

I started down the longer track and tripped over on the change at the end of
the year ... hence trying to cater for the error. Originally, it was just:
=DATEVALUE("01/"&MONTH(A1)+1&"/"&YEAR(A1))

I new there would be a simpler way ... it's just so embarrassing when
everyone else points it out

Regards

Trevor
 
Good God Trevor - don't be embarrassed
You are a VanGough compared to a Rembrandt
a Vivaldi to a Mozart
or dare I say a Jimmy White to a Steve Davis !

Keep the faith !

Cas
 
Sweet talker, you !

Cheers


Cas said:
Good God Trevor - don't be embarrassed
You are a VanGough compared to a Rembrandt
a Vivaldi to a Mozart
or dare I say a Jimmy White to a Steve Davis !

Keep the faith !

Cas

end
=IF(ISERROR(DATEVALUE("01/"&MONTH(A1)+1&"/"&YEAR(A1))),DATEVALUE("01/01"&"/"
Click to expand...
 
Hi Cas!

Re: "VanGough compared to a Rembrandt"

If you want the Picasso, use:

=TEXT(((MONTH(A1)-1)+12*(MONTH(A1)=1))*29,"mmm")&"-"&YEAR(A1)-(MONTH(A1)=1)

<vbg> Which I think is what Picasso permanently had when listening to
art critics.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top