Excel – Date format worldwide in Excel

cell formatdate timeinternationalmicrosoft excel

On a Mac Mountain Lion, US locales, I'm working on a Spreadsheet with macros like

TEXT(TODAY(), "yyyy-mm-dd")&" is the date"

The problem is that the sheet is also used on computers set with French locale, where the macro expects

TEXT(TODAY(), "aaaa-mm-jj")&" is the date"

and displays #VALUE (since a is for annee/year and j is for jour/day in French, not y and d).

Is there a way – macro style – to make this "yyyy-mm-dd" format more international, or to force Excel to use the "ymd" format even on a Mac using French locale?

(if possible doing this in the same cell, ie without adding a new cell having a date inside then referenced in the displayed cell, since I want to modify the sheet as less as possible)

Best Answer

I have the same problem ; here is the answer posted to my request Here

====================================
Private Sub Workbook_Open()
With Application
    Select Case .International(xlDateOrder)
        Case 0   'mdy
            ThisWorkbook.Names("DateFormat").Value = _
                .International(xlMonthCode) & _
                .International(xlDateSeparator) & _
                .International(xlDayCode) & _
                .International(xlDateSeparator) & _
                .International(xlYearCode) & _
                    .International(xlYearCode)

        Case 1  'dmy
            ThisWorkbook.Names("DateFormat").Value = _
                .International(xlDayCode) & _
                .International(xlDateSeparator) & _
                .International(xlMonthCode) & _
                .International(xlDateSeparator) & _
                .International(xlYearCode) & _
                    .International(xlYearCode)

        Case 2 'ymd
            ThisWorkbook.Names("DateFormat").Value = _
                .International(xlYearCode) & _
                    .International(xlYearCode) & _
                .International(xlMonthCode) & _
                .International(xlDateSeparator) & _
                .International(xlDayCode)
    End Select
End With
End Sub

You just have to define the name "DateFormat" in the workbook, then use it in formulas as =TEXT(A1,DateFormat)

Regards

Related Question