Calculate next year

[navin]

Hi All,

I have a sheet, in which Col A contains the list of month from Jan-
Dec. I have to list down the month along with its year based on the
month selected from the list in col A.

So if Apr is selected then, in col B2 to B13 i have should have
values displayed as:

Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07
Jan-08
Feb-08

I am unable to get the year, as after reaching Dec, year has to change
from 07 to 08. Please help me.

Thanks,
Navin

 

[Guest]

Try a formula like this

=date(year(A1),month(A1)+1,day(A1))

to get the next month in the sequence.

 

[navin]

Thanks for the reply.

I tried the formula, it gives me the next month, but after Dec, the
next cell should show, Jan-08. How to get this value?

Thanks again for the help
navin

 

[Ron Rosenfeld]

Hi All,

I have a sheet, in which Col A contains the list of month from Jan-
Dec. I have to list down the month along with its year based on the
month selected from the list in col A.

So if Apr is selected then, in col B2 to B13 i have should have
values displayed as:

Mar-07
Apr-07
May-07
Jun-07
Jul-07
Aug-07
Sep-07
Oct-07
Nov-07
Dec-07
Jan-08
Feb-08

I am unable to get the year, as after reaching Dec, year has to change
from 07 to 08. Please help me.

Thanks,
Navin

How are you "selecting" the month in Col A?

If your list of months is in Col A list as:

Jan
Feb
....

And in B1 you have a drop down list for the selection (Data/Validation/Settings
Allow: List
Source: =$A$1:$A$12

Then you could enter this formula in B2 and copy/drag down as far as needed:

B2: =DATE(YEAR(TODAY()),MATCH($B$1,$A$1:$A$12,FALSE)-2+ROWS($1:1),1)


--ron

 

[Niek Otten]

That happens automatically. Did you try?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thanks for the reply.
|
| I tried the formula, it gives me the next month, but after Dec, the
| next cell should show, Jan-08. How to get this value?
|
| Thanks again for the help
| navin
|
| Barb Reinhardt wrote:
| > Try a formula like this
| >
| > =date(year(A1),month(A1)+1,day(A1))
| >
| > to get the next month in the sequence.
| >
| > "navin" wrote:
| >
| > > Hi All,
| > >
| > > I have a sheet, in which Col A contains the list of month from Jan-
| > > Dec. I have to list down the month along with its year based on the
| > > month selected from the list in col A.
| > >
| > > So if Apr is selected then, in col B2 to B13 i have should have
| > > values displayed as:
| > >
| > > Mar-07
| > > Apr-07
| > > May-07
| > > Jun-07
| > > Jul-07
| > > Aug-07
| > > Sep-07
| > > Oct-07
| > > Nov-07
| > > Dec-07
| > > Jan-08
| > > Feb-08
| > >
| > > I am unable to get the year, as after reaching Dec, year has to change
| > > from 07 to 08. Please help me.
| > >
| > > Thanks,
| > > Navin
| > >
| > >
|

 

[Guest]

If Dec 2007 is in A1, try it and see what you get. I know it's worked for me
over and over.

 

[navin]

it worked perfectly.

thanks for the help.

navin