I recently returned from two weeks in Ireland where I installed a couple of Excel reporting systems and came across the dreaded regional date settings. My computer is set for the US region, whereas the client’s computers were set for the UK region. When I mention regional settings on my computer, I’m talking about Windows and not Excel.
Trying to get Excel to automatically switch date formats between computers using different region settings was something I experienced a few years ago while working in England. Back then I made the mistake of ustng Excel to change the formatting for my dates. Not the way to go.
Regional Date Formats
The US Short Date format is represented in the form “m/d/yyyy” which means dates are shown in a “Months/Days/Years” format. Contrast that with Ireland, the UK, and most everywhere else, where the date format is “dd/mm/yyyy” and takes the form Days/Months/Years.
Using the Default Short Date Format in Excel
Since I live in the US my computer’s regional settings are set for the US. When formatting dates in Excel for files that may be used internationally, it’s wise to use date formats that begin with an asterisk (*) per the Format Cells Dialog box.
Any date formatted in this manner will display correctly in another region. I can view the date June 13, 2011 in my spreadsheet as 6/13/2011 and send it to a person using a UK regional setting on their computer and the date will automatically display as 13/6/2011.
Nice and neat. We’re all speaking the same language here.
How Not to Change a Date Format for a Different Locale (Region)
Let’s say you are in the US and are designing a spreadsheet that will be used in the UK. You want to see “what they see” and decide to change the cell formatting in a column that contains dates. You select the range, open the Format Cells dialog box (Ctrl+1) then select English (U.K.) from the Locale (Location) drop-down list, and click OK.
Bad move.
First of all, look at the picture above and notice there are no date formats that have a leading asterisk (*) which means that no date format will survive a change to a different region. You are stuck with this format in every region, whether it’s relevant or not.
How to Change Region Settings in Windows
The proper way to “see” a different region’s date format is to change your computer’s Region setting. Click the Windows Start button and select Control Panel. Find and click the link for Clock, Language, and Region.
Next click the link for Change the date, time, or number format.
On the Region and Language dialog box click the Formats tab.
In the Format drop-down box select the Region of your choice. In my case that’s English (United Kingdom).
In the screen shot above you can see the Short Date format is dd/MM/yyyy. Click OK to set your computer’s region.
Now open Excel and all dates previously formatted with the default Short Date format for the US (*3/14/2001) will show up in the Short Date format for the UK.
To check this fact, select a cell with dates, then bring up the Format Cells dialog box (Ctrl+1) to see that the Short Date format in Excel has automatically changed to *14/3/2001.
This is how you change the date formats for different regions.
Customize the Region Date Format in Windows
I don’t like having two digits showing for the day and month in the Short Date format. To me 4 July, 2011 should be represented as 4/7/2011 and not as 04/07/2011. However that’s what you get when you select the English (United Kingdom) region setting.
I used a custom format for the UK region short date setting to rid my spreadsheet of the unnecessary zeros. To do this you bring up the Region and Language dialog box, as we did previously, and click Additional Settings… then select the Date tab.
Type d/M/yyy in the Short Date box and click OK.
Now all months and days with single digits show up without a leading zero in Excel when using the short date format.
I have “English – India” as the regional format on my pc, and the short date format is set as dd-mmm-yyyy.
I don’t usually need to deal with files that are shared internationally.
Occasionally I have to work with dates formatted in the US-style.
For such files, I format the dates as dd-mmm-yyyy.
This format leaves no doubt about the date.
After making this change, I still check the correctness of the date cells using a few other techniques and business logic.
However, I’m curious to know whether a file (with dates formatted like this) sent to a user with a different regional date format setting would display wrongly on her system ?
Khushnood
If you use Excel to change the date format to dd-mmm-yyyy and there is no asterisk (*) in front of that format, then when you send the file to someone in a different region they will see the same date format.
However, the underlying Serial Date remains the same so all they have to do is change the date format to something they recognize in their region and the correct date will display.
I thought that would be the case; so its good to be reassured that the date will display in the custom format, irrespective of the regional settings.
I feel that dd-mmm-yyyy is a better format, in that it leaves no room for doubt on either side 🙂
Thanks for the tip.. worked like charm for me! regards Aniket
Hi Gregory, have a question related to date formats..
I need my date formats to change according to the user’s regional settings, such as using an *format as described in your article.
The problem is that there are only a couple of *formats, and they don’t always fit my needs, so I end up using custom formats… but then they don’t update (as far as I know)
So the question… is there a way to create a custom format that updates with regional settings (i.e. a custom *format)??
Thanks,
ET
I wrote another article that applies custom format, which might be useful to you. It requires a bit of VBA and uses the Application.International property.
Tks Gregory.
Not ideal, but certainly a valid solution I was not aware of, and the best option I have so far.
ET.
I have exported some data files in to excel, and now excel reads some dates to month and months to date, I am not able to understand why is this happening, first time I have come across such situation. I am from India and here format is dd/mm/yyyy and data was also in this format and now, when it got exported excel is reading mm to dd. Earlier I used to get month wise details by data filtration but now it is showing mixed aberrant results, i am totally puzzled. Please help in a easiest way.
Either the cell formatting is incorrect or Excel is not recognizing the Date as a valid Date value.
Select all the dates, right click the selection, select Format Cells, click the Number tab, select General, and click OK. All your dates should now be a 5 digit number. If some are not a 5 digit number, then Excel is not recognizing those cells as a valid date format. I would highlight any cell that is not a 5 digit number so you can recognize it after the next step.
Select all the dates again, right click the selection, select Format Cells, click the Number tab, select Date, and choose the default short date format (it’s typically the top entry and has an asterisk (*) before it). Short date format is like dd/mm/yyyy or mm/dd/yyyy depending on your computer’s regional format setting.
For any of the cells that you highlighted, there are a few things that you can do. Try the DATEVALUE Function and see if that will convert it to a 5 digit date serial number, which then you can format as a Date, then Copy and Paste as Values into the original cell.
You might be able to enter the numeric value 1 into a cell, copy that value, select the highlighted cells, right click and select Paste Special, choose Values and Multiply, and then click OK. This action will sometimes trick Excel into recognizing the Date by performing a mathematical operation on it. (This doesn’t change the value.)
Sometimes if you simply edit the cell, by selecting F2 (or Control+U on a Mac) and hit enter, Excel will suddenly recognize the date.
Lastly you can retype the date manually.
It’s been my experience that Dates that are exported from some programs (databases are the worst) can have a type of formatting that Excel does not recognize. In fact, sometimes the formatting acts like it’s not from this world. 🙂 I’ve had to type a Date into a new cell then copy that value and paste over the original value to make things right.
Hope this helps.
thanks for the solution Gregory.. the screenshots are really helpful.
Hi Gregory
I found this article very helpful, Thanks for all the tips!!
I’m facing a issue regarding date separtors, We have designed our excel sheets to have date format as mm/dd. Some of the users, say Swedish they have format as mm-dd… is there any solution for this to lock our date field to mm/dd.
You might try and format all cells with date values with a custom format of m/d;@ and then when someone enters 12-3 for a date it will appear as 12/3.