Using the Text function with German and English regional settings

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi there...

my friend Andreas and me were involved, IMHO, in an interesting discussion on LinkedIn about the behaviour of Excel when using the TEXT() function to retrieve the months of a given time/date.

With German Windows regional settings, I am able to retrieve the minutes by using TEXT(NOW(), "mm") and the month by using TEXT(NOW(), "MM"). In this case, Excel differenciates between lower and upper cases of the letter M. If I change the Windows system regional settings to English (US or UK), both TEXT(NOW(), "mm") and TEXT(NOW(), "MM") return the month.

So my question is, am I right, if I say that it is not possible to retrieve the minutes by only using the TEXT() function with non-german regional settings? (without appending or prepending other format strings like hh) Or did I oversee something?

Thanks & Best Regards :)
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
According to help ...

The m or the mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
You are right, this is mentionned in the help and I have also seen it. However this does not explain why Excel differenciates between "MM" and "mm" with german regional settings. On german regional settings I am not able to retrieve the month by using only "mm", I must use "MM".
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Your original question was ... am I right, if I say that it is not possible to retrieve the minutes by only using the TEXT() function with non-german regional settings? (without appending or prepending other format strings like hh) Or did I oversee something?

You did not originally ask why it is different with German regional settings. I answered your question by pointing out what help said, and confirming that you were right, you cannot do it with our settings.
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi,
I answered your question by pointing out what help said, and confirming that you were right, you cannot do it with our settings.
Ok, sorry, I did not realize from your first answer that you meant what you said in your second answer. Thanks :)
 
Top