Hi,

I have tried out a forumla from a book to change a date to a shortened month and year.

The date in cell B2 looks like this

01-01-2007

The formula is:

=TEXT($B2;"mmm")&CHAR(13)&TEXT($B2;"yy")

The result I get is

jan (square) yy

I would really like to have the yy to be 07.

I know the &CHAR(13) is meant to give me the square. This will allow the year to be shown in the next line in a chart

Just what am I doing wrong to prevent me getting the 07 from the year?

I am using Excel 2003. Thanks for any help. Have a good evening.

Graham

Hey Graham,

Welcome to PCHF. We have a wonderful team of techs here, and I feel certain that we will be able to help you determine why you are having this trouble.

Here is the formula as I entered it into Excel
=TEXT($B$2,"mmm")&CHAR(13)&TEXT($B$2,"yy"), I also tried it with only the B absolute reference you have, and no absolute reference at all. All three ways I got the result you are looking for.

Try this, first, make sure that you have not overlooked the comma between the B2, and the "yy". Then make sure that you don't have any extraneous commas. If neither of those are true, try adding the letters SET to the end of CHAR, this will bring up a function error code, during the process of resetting the function, Excel may reanalyze what it is supposed to be returning.

Looking forward to your reply,

TTFN

LGW

Hi ladygreenwitch,

Thanks for the help.

I finally found the probelm when a friend in the states sent me his version of the file that works. Whilst his file gave the correct result when I copied the formula to another cell I got the error again.

Then on the train home I thought that maybe it shouldn't be yy but åå - this beging the first letter for the Danish word year = år.

When I tried this (as well as making the commas into semi-colons) the formula worked.

It has made me aware of the difference that are included when you are using a Danish system with Excel on.

Being an English ex-pat in Denmark I ahve not grouwn up with the Danish version, so it's back to school again for me

Hi Graham DK,

Im glad you have your problem sorted now, marked as resolved.

Chris