2

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)

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

| improve this answer | |
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.