Member Panel


Sponsors and Ads

Join the Team

Live Tag Cloud

PC Forum PC Help Forum » Software » General Software » [Resolved] Excel and formula for =TEXT : can you help?

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 ...

JOIN US NOW to remove these Ads

Post New Thread  Reply
  #1  
Old 03-14-2007
Bronze Member
 
Join Date: Feb 2007
Posts: 6
Graham DK - See this Members User comments on their Profile page
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


  #2  
Old 03-16-2007
ladygreenwitch's Avatar
Administrator
 
Join Date: Jul 2005
Location: Bay Area California
Posts: 4,694
ladygreenwitch - See this Members User comments on their Profile page ladygreenwitch - See this Members User comments on their Profile page ladygreenwitch - See this Members User comments on their Profile page ladygreenwitch - See this Members User comments on their Profile page
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


  #3  
Old 03-25-2007
Bronze Member
 
Join Date: Feb 2007
Posts: 6
Graham DK - See this Members User comments on their Profile page
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


  #4  
Old 03-25-2007
upgrader's Avatar
Site Manager
My PC
 
Join Date: Jul 2006
Location: /home/upgrader/
Posts: 6,324
PC Experience: Some Experience
upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page upgrader - See this Members User comments on their Profile page
Send a message via MSN to upgrader Send a message via Skype™ to upgrader
Default

Hi Graham DK,

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

Chris


__________________
PCHF Rules--PCHF Prework--PCHF Downloads

Reply
Satellite TV on your PC - over 3000 Channels! Click Here!

Bookmarks

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 +1. The time now is 10:24 AM.
Powered by vBulletin
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.2.0 RC7
All Graphics & Content Copyright © 2004-2008 - PC Help Forum.com


Back to Top
Pay Day Loans
Pay day loans information and advice from Moneyey expert.

Mortgages
Mortgage information and advice.

Apartments in Budapest
Budapest Accommodation Service