In on cell I'm showing the full date (like 01.01.2013 > German format) and in another cell I want to show only the day of week. So I followed the instructions from Microsoft which refer to the TEXT function. But somehow it never works. If I use "ddd" or "dddd" as format string, I get exactly those letters as return value.
Any idea of to fix that?
Cell A1:
31.12.2012
Cell A2: (German locale)
=TEXT(A1; "ddd")
The results are ddd
You have to use german TAG t instead of english DAY d if your locale is set to german
=TEXT(A1;"ttt")
In Control Panel go to Regiona and Language and change it to German
then go to your Excel spreadsheet, click any cell with a date in it and select Format Cells. Go to Custom and if you look at the date in german it doesn't use dd mm yyyy it uses german Tag Monat Jahr which oviously is day month year
Answer is from mehow in the comments of this post
if youre in german locale then you have to use ttt instead of ddd Tag = Day
I think the issue is Excel doesn't know that 31.12.2012 is a date.
If I type in
31/12/2012
instead of
31.12.2012
Then it works as expected.
When using 31.12.2012, if I make the cell type 'date' it doesn't work as you want. Note that when Excel can see it as a date, it right aligns it in the cell. In the following screen, I made the B1 of type date but, it doesn't treat it as such:
You could also try to use
=TEXT(WEEKDAY(A1),"dddd")
or
=TEXT(WEEKDAY(A1);"dddd")
(; or , depending on locale)
ttt instead of ddd Tag = Day :)
For German locality I believe you need to replace commas with semi-colon
So the formula would be:
=TEXT(A1;"ddd")
