Locale Based Date Formula

**kranthis11** · 08-10-2012, 03:04 PM

Hi all,

I am trying to build a spreadsheet which needs to be used by global users. I am in US locale and I am converting a value in a cell B11=40999 to
a date format by using =MONTH(B11)&"/"&DAY(B11)&"/"&YEAR(B11) and I get a value '3/31/2012'. This format gives an error in the UK locale and they need to get the value as '31/3/2012'.

Is there a way I can create a formula based on the locale so that if the locale is Non-US I can use =DAY(B11)&"/"&MONTH(B11)&"/"&YEAR(B11).

Thanks in Advance

**shg** · 08-10-2012, 03:27 PM

You could format the date unambiguously:

=TEXT(B11, "d mmm yyyy")
=TEXT(B11, "yyyy-mm-dd")

**kranthis11** · 08-10-2012, 03:32 PM

I presented this work around too, but this format is not an approved format for our company.

**shg** · 08-10-2012, 04:21 PM

I don't know of another way unless your workbook already contains macros (I wouldn't add a macro just to meet this requirement).

You could have your users change a named constant that contains a date format string used throughout the workbook, but that would require conscious effort on their part.

I have no other suggestions.