=TEXT formula
Hi everyone,
I have been re-teaching myself some excel formulas from a booklet I have for the older version of excel and trying to apply it to excel 2007.
I have been trying to use an =TEXT formula to convert a date into a day.
Example:
11.09.1956
I have typed in =TEXT(A1;"dddd") but this doesn't work.
Can anyone help me?
Thanks
Last edited by nevillina; 03-12-2010 at 07:02 PM.
Is that date entered as a text string?
Try: =TEXT(Substitute(A1,".","/");"dddd")
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
No, the date is in numbers: 11.09.1956
I tried your formula but it says that I have entered too few arguments.
Sorry, regional setting differ...
=TEXT(Substitute(A1;".";"/");"dddd")
It still comes up in the cell as dddd instead of a day of the week.
How does the attach translate?
In cell B1 it says dddd
Delete... bad formula...
Last edited by NBVC; 03-12-2010 at 05:38 PM.
It stills says dddd. Here is the attachment:
for me the basic =Text(C2,"dddd") works...
I am not sure why even the one I sent you pulls up dddd
Does the attached work?
No, it stills comes up as dddd...it's very strange as I have tried DATEDIF functions with the same worksheet and they work fine. It's only the =TEXT that doesn't.
Thanks for your help anyway.
Sorry have never witnessed that before...
Have you tried just hitting F2 on the cell and then Enter?
The file you sent showed me dddd too, but I just activated and hit enter and got a day...
Note: The formula I gave you before ...=Text(Right(A2,2).....) was rubbish.. it is but the basic =TExt(A1,"dddd") should work....
Another way would be just to enter =C2 and then format the cell as custom: dddd
My computer did the same thing as NBVC's. If you have a lot of that formula in the column, rather than using F2 on each one, you can select the column and do Text to Columns>Finish. That changed the dddd to Tuesday for me. (Text to Columns is on the Data tab of your ribbon).
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Tried both of your suggestions, but I still get the same dddd answer. I think I might try it on someone else's computer and see if it works. Maybe it is just not working with my Excel.
Thanks for your help. : )
I tried both of NBVC's suggestions and the good old dddd pops up again. And I tried your suggestion too ChemistB but still nothing.
I think it must be my computer. I'm going to try it again on someone's computer and see if it works...i'm sure it will, just not on mine.
Thanks for all of your help! : )
I just found out why it wouldn't work. Although my excel is in english and my computer, i bought it in Switzerland (the french part) and dddd would be english, i typed in jjjj (for jour) and it comes up with the answer in french. So it does work, just unfortunately for some reason it is in french....problem solved, apart from i don't know how to change that back into english!
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
nevillina 
Register To Reply
Bookmarks