Our November Competition
User Reviews - Add Yours!
The PCHF Lounge
Go Back   PC Help Forum » Software » General Software
Register for a Free Account

General Software - [Resolved] Excel and formula for =TEXT : can you help? posted in the Software forums; 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 ...


Reply
Scan your PC for Errors
Old 03-14-2007   #1
Bronze Member
 
Join Date: Feb 2007
Posts: 20
Default [Resolved] Excel and formula for =TEXT : can you help?

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
Graham DK is offline   Reply With Quote
Advertisement - Register to Remove
Old 03-16-2007   #2
Elite Member
 
ladygreenwitch's Avatar
 
Join Date: Jul 2005
Location: Bay Area California
Posts: 6,625
PC Experience: Very Experienced
Default

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
ladygreenwitch is offline   Reply With Quote
Old 03-25-2007   #3
Bronze Member
 
Join Date: Feb 2007
Posts: 20
Default

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
Graham DK is offline   Reply With Quote
Old 03-25-2007   #4
Site Manager
 
upgrader's Avatar
 
Join Date: Jul 2006
Location: /home/upgrader/
Posts: 7,250
PC Experience: Some Experience
Default

Hi Graham DK,

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

Chris
__________________
PCHF Rules--PCHF Prework--PCHF Downloads
upgrader is offline   Reply With Quote

Reply


Bookmarks

Tags
excel, formula, Resolved, text

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are On




All times are GMT. The time now is 08:01 AM.
Powered by vBulletin
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.3.2