Excel: show day of week does not work (German locale)
Solution 1
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
Solution 2
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)
Solution 3
For German locality I believe you need to replace commas with semi-colon
So the formula would be:
=TEXT(A1;"ddd")
Related videos on Youtube
Matthias
Updated on September 18, 2022Comments
-
Matthias over 1 year
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
-
Dave over 10 yearsCan you paste the exact function/code you're using please.
-
Matthias over 10 yearsI added the functions above
-
Dave over 10 yearsCan you confirm what the type of Cell A2 is (text/general etc). Also, did you type the code out or copy it from the MS site?
-
Matthias over 10 yearsA1 is date and A2 is of type general. I typed the formular into Excel by myself but following the descriptions on the Microsoft website.
-
-
Matthias over 10 yearsMy Windows and Excel versions are both US but keyboard and locale is German. So Excel automatically modified 31/12/2012 to 31.12.2012. It knows that it's a date.
-
Matthias over 10 yearsI use ; instead , Otherwise Excel does not except the formular anyway. Fixed it above.
-
Admin over 10 yearsno, if youre in german locale then you have to use
ttt
instead ofddd
Tag = Day :) -
Matthias over 10 yearsThanks, that did the trick. Even though all formulars are in English, I need to use "ttt" as format string. Thanks. By the way: in German locale 31.12.2012 is automatically accepted as a valid date.