3

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)

fixer1234
  • 28,064
Déjà vu
  • 703

2 Answers2

1

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

user279809
  • 11
1

I've just written my own user-defined function as

Public Function DATE_FORMAT(dt as Date, fmt as String) as String
    DATE_FORMAT = Format(dt, fmt)
End Function

It uses the fact that VBA works with US locale at all times, so I can always use D and M and Y as part of my format string.

You can then use the Excel formula =DATE_FORMAT(A1, "YYYYMMDD") even in non-US/UK locales.

MP24
  • 111