Date format for Excel

October 22, 2009 at 10:31:37
Specs: Windows Vista
I hope someone can help me here.
I am creating a spreadsheet that will be opened and used in mulitple countries. I have formatted the date cells for mm/dd/yy and set to US format. When this same spreadsheet is opened in Australia, the date is input the same format but switches to dd/mm/yy when exiting the cell.
Is there a way to lock the US format so that it does not change in other countries?
Thanks for any help you can provide!

See More: Date format for Excel

Report •

October 22, 2009 at 11:13:27

This is not a solution, just a workaround:

If you format the date with mmm (Oct) rather than mm (10), it will be clear to users in all countries which is the month and which is the day.

BTW is this
a single spreadsheet on a server accessed from different countries or
is it a spreadsheet that is e-mailed back and forth, or
is it several copies of an original that are sent out and returned, or
something else!

I ask this because it could affect the solution.


Report •

October 22, 2009 at 11:28:48
Thanks for the info! I will see if this will help.

The spreadsheet will be downloadable from a server to be opened and used in multiple countries.

Report •

October 22, 2009 at 11:37:49
Just a comment:

You understand that how a date is formatted is only cosmetic
Excel stores the date as a number,
IE todays date is 10/22/2009 or if you prefer 22/10/2009 but Excel stores it as: 40108 which is the number of Days since Jan 1, 1900.


Report •

Related Solutions

October 22, 2009 at 11:54:34

As Mike says, dates are only cosmetic.

You could include code in the 'Before Close' event which reformats all dates in your desired format, but that might result in errors as users in Australia etc. assume that dates are in the logical day-month-year order, rather than the US standard month-day-year.

You could add two buttons with some code.
One button formats all dates per US standard and the other button formats all dates per day-month-year format.

This sample code shows that it would not be too complicated to do, then wherever the spreadsheet is opened the user can see dates in their preferred format:

Sub NADateFormat()
Dim rngCell As Range
For Each rngCell In Worksheets("Sheet1").UsedRange
    If IsDate(rngCell.Value) Then
        rngCell.NumberFormat = "mm-dd-yy"
    End If
End Sub

The sheet name will need to be changed to match yours.
Also the code would need to be altered if more than one sheet in the workbook has dates in it.


Report •

October 22, 2009 at 11:59:10
Thank you both for your input!

This is a great forum site! :-)

Report •

October 22, 2009 at 12:32:17
You might be able to automate Humar's suggestion by checking the Country Code for each machine upon opening the spreadsheet.

See here for more info, including this sample code:

Sub Code()
   Country_Code = Application.International(xlCountryCode)
   If Country_Code = 1 Then
      MsgBox ("Hello")
   ElseIf Country_Code = 34 Then
      MsgBox ("Hola")
   End If
End Sub

The site offers a list of Country Codes returned by


which you could then use in a list of Select Case or If options to format the dates using Humar's NumberFormat sugestion.

Check out the VBA Help files for the International Property to see if there is anything else in there you can use.

Report •

Ask Question