Number Format

[mars]

Hi all
I previously asked about a formula for updating a year (apologies for
not being able to link the post but I couldn't find it on google
rather on an external website (Excelbanter)).
The post read:
I want to enter a year in a cell in this format "2006/07" and have
several cells linked to this which will then populate in steps
i.e. based on the year I have entered they will increase annually thus
2007/08, 2008/09 et seq.

I received two very helpful suggestions (below) but found consequently
that neither can cope with the year "1999/00" - it shows up as
"1900/01" instead of "2000/01".

Is there another solution I wonder?

Thanks in advance

Mars


=LEFT(A1,2)&RIGHT(A1,2)&"/"&TEXT(RIGHT(RIGHT(A1,2)+1,2),"00")

=LEFT(A1,2)&RIGHT(A1,2)&"/"&TEXT(RIGHT(A1,2)+1,"00")

 

[Junge]

Try
Left(A1,4)+1 &"/"&Right(Left(A1,4)+2,2)
Junge

 

[steve]

Hi if you format the columns where your dates are to a custom format
"YYYY/MM" that should take care of how the dates are shown and then anything
you do from there should work.

Regards
Steve

 

[mars]

Thanks for your replies.
Junge that did the trick, Steve these are years not month/year so
formattting would not work. Sorry I didn't make it clear.

Mars

 

[steve]

OK I see it now.
=LEFT(A2,4)+1&"/"&RIGHT(A2,2)+1
would do the trick

Regards
Steve