Calculate next year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

navin - 20 Apr 2007 11:50 GMT

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

Reply to this Message

Barb Reinhardt - 20 Apr 2007 13:46 GMT

Try a formula like this

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

to get the next month in the sequence.

> Hi All,
>
[quoted text clipped - 23 lines]
> Thanks,
> Navin

Reply to this Message

navin - 20 Apr 2007 14:15 GMT

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

> Try a formula like this
>
[quoted text clipped - 29 lines]
> > Thanks,
> > Navin

Reply to this Message

Niek Otten - 20 Apr 2007 14:35 GMT

That happens automatically. Did you try?

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thanks for the reply.
|
[quoted text clipped - 37 lines]
| > > Thanks,
| > > Navin

Reply to this Message

Barb Reinhardt - 20 Apr 2007 14:38 GMT

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

> Thanks for the reply.
>
[quoted text clipped - 37 lines]
> > > Thanks,
> > > Navin

Reply to this Message

navin - 20 Apr 2007 16:02 GMT

it worked perfectly.

thanks for the help.

navin

> If Dec 2007 is in A1, try it and see what you get. I know it's worked for me
> over and over.
[quoted text clipped - 40 lines]
> > > > Thanks,
> > > > Navin

Reply to this Message

Ron Rosenfeld - 20 Apr 2007 14:17 GMT

>Hi All,
>
[quoted text clipped - 23 lines]
>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

Reply to this Message