Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Can you paste the exact function/code you're using please. –  Dave Rook Aug 28 '13 at 8:31
    
I added the functions above –  Matthias Aug 28 '13 at 8:34
    
Can 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? –  Dave Rook Aug 28 '13 at 8:46
    
A1 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 Aug 28 '13 at 8:53

3 Answers 3

up vote 1 down vote accepted

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.

enter image description here

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:

enter image description here


You could also try to use

=TEXT(WEEKDAY(A1),"dddd")

or

=TEXT(WEEKDAY(A1);"dddd")

(; or , depending on locale)

share|improve this answer
    
My 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 Aug 28 '13 at 8:41
1  
no, if youre in german locale then you have to use ttt instead of ddd Tag = Day :) –  me how Aug 28 '13 at 8:47
    
Thanks, 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. –  Matthias Aug 28 '13 at 8:50
    
@Matthias, made an update –  Dave Rook Aug 28 '13 at 8:51

For German locality I believe you need to replace commas with semi-colon

So the formula would be:

=TEXT(A1;"ddd")
share|improve this answer
    
I use ; instead , Otherwise Excel does not except the formular anyway. Fixed it above. –  Matthias Aug 28 '13 at 8:43

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

ok

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

see

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.