Using the Text function with German and English regional settings

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

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.

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".

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.

Hi,

I answered your question by pointing out what help said, and confirming that you were right, you cannot do it with our settings.

Click to expand...

Ok, sorry, I did not realize from your first answer that you meant what you said in your second answer. Thanks