Regional Date Format in Excel for Mac and Windows

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 using 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.
Regional Date Formats

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.

Date Format Settings Excel Windows

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.

Excel Local Date format UK

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.

Windows Regional Settings (Navigation to)

Next click the link for Change the date, time, or number format.

Windows Control Panel Regional Format Settings

On the Region and Language dialog box click the Formats tab.

Windows Region and Language Dialog Box

In the Format drop-down box select the Region of your choice. In my case that's English (United Kingdom).

Windows Region English UK

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.

Windows Format Cells Date UK

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.

Customize Regional Date Format 1

Type d/M/yyy in the Short Date box and click OK.

Customize Regional Date Format 2

Now all months and days with single digits show up without a leading zero in Excel when using the short date format.

How to Change Region Settings on Mac

Step 1: Change Regional Settings on Your Computer

Open System Preferences > Language & Region.
Select your desired region from the list.
Close and restart Excel to apply the new settings.

Adjusting your system’s regional settings on a Mac ensures that all Apple-native applications, including Excel, use the appropriate date format. This is especially helpful when working on long-term projects involving multiple file types.

Step 2: Change Date Format in Excel

Open your Excel spreadsheet.
Select the cells containing the dates you want to format.
Press Command + 1 to open the Format Cells dialog.
Go to the Number tab, select Date, and choose the format that suits your needs.
Click OK to confirm the changes.

Best Practices for Managing Dates in Excel

Use ISO Date Format (YYYY-MM-DD) when sharing files internationally. This format is universally understood and reduces confusion.
Be Consistent: Always ensure your entire dataset follows a single date format.
Check Regional Settings: Before collaborating, check and agree on the regional format with your colleagues.
Use Data Validation: Prevent incorrect date entries by setting up data validation rules for date fields.
Document Your Choices: Add notes or comments to your spreadsheet explaining the date format used, especially when collaborating with others.

Conclusion

Understanding and managing regional date formats in Excel is essential for clear communication and accurate data analysis. Whether you're using Excel on Mac or Windows, adjusting these settings is straightforward once you know where to look.

By mastering date formats and employing best practices, you’ll reduce errors and enhance your productivity when working with international teams. Proper date management in Excel is more than just a convenience—it’s a vital part of effective data handling and collaboration.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

Khushnood ViccajiJune 22, 2011 at 09:28 AM

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

Gregoryexcelsemipro.comJune 22, 2011 at 06:24 PM

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.

Khushnood ViccajiJune 23, 2011 at 04:35 AM

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 :)

Aniket KothurwarAugust 4, 2011 at 09:40 AM

Thanks for the tip.. worked like charm for me! regards Aniket

ETSeptember 30, 2011 at 04:01 PM

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

Gregoryexcelsemipro.comOctober 1, 2011 at 03:16 PM

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.

ETOctober 2, 2011 at 03:37 AM

Tks Gregory. Not ideal, but certainly a valid solution I was not aware of, and the best option I have so far. ET.

PiyushJuly 4, 2012 at 03:06 PM

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.

Gregoryexcelsemipro.comJuly 4, 2012 at 04:28 PM

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.

HimanshuAugust 30, 2012 at 04:45 AM

thanks for the solution Gregory.. the screenshots are really helpful.

TahirDecember 6, 2012 at 11:28 AM

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.

Gregoryexcelsemipro.comDecember 9, 2012 at 01:33 AM

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.