I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.
Here’s what a US user sees:
-
From: 6/6/2011 to 6/10/2011
Here is the formula:
-
=”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)
The Min and Max dates are in column A on a worksheet named ExtractData.
The problem is trying to automatically change the date format in the second argument of the TEXT Function – “m/d/yyyy” – which is a string argument. We can use VBA to accomplish this, but first a refresher on the TEXT function syntax.
The Solution
The Application.International Property solves this problem with the xlMDY argument, which is TRUE if the date order is month-day-year, and FALSE if the date order is day-month-year. This property is put into the Workbook_Open event and modifies a defined name constant that’s used for the second argument of the TEXT function.
Here’s how it’s done.
Create a Defined Name Constant for the Date Format
I created a Defined Name Constant to store a Short Date format, and gave it the name sd_format.
In Excel 2007 and 2010, choose Formulas > Define Name to bring up the New Name dialog box where you type in sd_format in the Name box, and type =”m/d/yyyy” in the Refers to box. Remember the equals sign.
In Excel 2003, 2008, and 2011 choose Insert > Name > Define to bring up the Define Name dialog box. Type sd_format in the Names in workbook box, then type =”m/d/yyyy” in the Refers to box. Be sure to use the equals sign.
Substitute the Named Constant in the TEXT Function
The sd_format defined name can now be substituted for “m/d/yyy” in the second argument of the TEXT function. Here’s the new formula:
-
=”From: ” & TEXT(MIN(ExtractData!A:A),sd_format) & ” to ” & TEXT(MAX(ExtractData!A:A),sd_format)
Since sd_format is already a text string, enclosed quotes are not needed.
Create a Workbook Open Routine
In the VBA Editor, I created a Workbook_Open subroutine, which looks at the computers international setting for the US, and if TRUE changes the sd_format value to “m/d/yyy”, and otherwise changes it to “d/m/yyyy” for the UK short date format.
Private Sub Workbook_Open()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This routing updates the regional date setting format for the
' defined name sd_format.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Application.International(xlMDY) = True Then
ThisWorkbook.Names("sd_format").Value = "m/d/yyyy"
Else
ThisWorkbook.Names("sd_format").Value = "d/m/yyyy"
End If
End Sub
How it All Works
Each time the workbook is opened the Workbook_Open routine executes the IF-THEN-ELSE statement, which simply looks to see if the computers region setting is month-day-year, then sets the defined name sd_format to “m/d/yyyy” which is a US format. If the computer’s region setting for the long date format is NOT month-day-year (and presumably day-month-year) then sd_format is set to “d/m/yyyy” for the UK.
Every TEXT function using sd_format for the second argument will then have the proper short date format for that computer.
Note: Obviously if the region settings are changed on the computer while the file is open the file will have to be closed and reopened, but this would most likely be a rare occurrence.