How to change date format in Excel 365 Online

How to change date format in Excel 365 Online

Are you having a problem with date formatting in Excel 365 online? For example, are you finding your dates are showing as an American date format of mm/dd/yyyy or maybe the opposite with a UK date format of dd/mm/yyyy?

In this blog I’ll take you through a few things you can check in Excel 365 Desktop and Excel 365 Online so that you can change your date format to the Excel date format you require.

[Watch on YouTube] / [Subscribe to our YouTube Channel]

Why is the Excel 365 Online date format not working?

You may be experiencing problems within your own files, files you share with others, or both.

Saving files into OneDrive or SharePoint is a fantastic way for team members to be updating and working with files from outside of the office, and an excellent way for everyone to have access to the same file either inside or outside of the office.

However, it can be super frustrating when data is entered incorrectly as this impacts everyone using the file, and fixing formatting problems can be such a waste of your precious time.

Different date formats

Our client’s team had a problem with date formatting in Excel 365 Online when some members were updating dates in a shared file in the Desktop version of Excel 365, and others were updating dates in the same file using Excel 365 online, the web browser version.

Some team members were using both Excel 365 for Desktop when working in the office, and Excel 365 Online when working from home.

When dates were entered using Excel 365 desktop the date format was correct.

Sadly, the date formats kept changing when anyone accessing the file using Excel 365 online updated the file.

The team continually battled with dates entered as mm/dd/yyyy when they would normally use the dd/mm/yyyy format.

Additionally, some dates were aligned to the left of the cell and some to the right and this was happening in both the desktop and web versions of Excel.

This is how we fixed it.

Tip #1 Check the Date format in Excel Desktop

The first thing we checked was the date formatting had been set correctly as dd/mm/yyyy in Excel 365 Desktop.

We work with a New Zealand date format of day/month/year, a.k.a dd/mm/yyyy, e.g. 31/01/2022.

Change the date format in Excel Desktop

1. To check the current date format first select the cell range containing the dates, and then right-click any cell in the selected range.

2. Click Format Cells.

3. On the Number tab click Date and then check that your date is set to the format you require. You can see in the image below that ours is set to dd/mm/yyyy and the Sample box shows the date as 31/01/2022 which is exactly how we are wanting it to be displayed.

Also, check that the Locale option is set to your location. Ours is set correctly with English (New Zealand).

Excel Online date format not working 

We found that the date settings were set correctly in Excel 365 Desktop.

However, even if we clicked OK to reapply the formatting on every selected cell, we still had several cells that were still formatted incorrectly with the date still left-aligned in the column.

We used the steps in Tip #3 below to reset the dates to the correct format.

Additionally, as soon as a team member accessed the file using Excel 365 Online the dates went back to being formatted incorrectly.

We then tried the steps in Tip #2.

Tip #2 Change the date format in Excel 365 Online

When team members were editing the sheet in the browser version of Excel (Excel 365 Online) the formatting changed to month/day/year, a.k.a mm/dd/yyyy, even though the date formats were set correctly in Excel 365 Desktop.

This was the reason some of the dates were being left-aligned in the column. For example, 31/01/2022 was being entered incorrectly because the web version of Excel 365 was seeing the date as mm/dd/yyyy. Since there isn’t a 31st month, Excel saw this as a mistype and entered the date as text, not a date.

However, because the date format was set to read month then day, entering 01/02/2022 was being entered as a date but it was being held as the 2nd of January, not the 1st of February.

Change the Date Format in Excel 365 Online

We followed the same steps as for Excel 365 Desktop to check the current Excel date format. To do this follows these steps.

1. Login to your Excel 365 Online account and open the file.

2. Select the cell range containing the dates, and then right-click any cell in the selected range.

3. Click Number Format.

4. On the Custom option we can see that the date format is set to dd/mm/yyyy. However, the dates were still being formatted incorrectly.

5. Click Date in the Category list.

6. Now, this is the important part. Check the Locale (location) setting. We had such a mix of settings for this. Some members of the team had English (United States), while others had Armenian or Azerbaijani!

7. Now select the correct date format you require from the Type list.

8. Select your location from the Locale (location) list.

9. BEFORE you click OK be sure to select the Set this locate as the preferred regional format option box. If you don’t do this the date format will change itself back to its former setting again.

10. When you click OK the file will update.

11. Once the new date format is in place all dd/mm/yyyy date entries will be formatted with the correct Excel date format. If you find existing date entries still haven’t updated, please check the steps in Tip #3 below.

Tip #3 Change existing dates to the correct date format in Excel

The tips above will set your date format for all dates entered going forward.
However, you may now be left with existing dates that are still in the incorrect format.

Let’s look at a quick way to fix this.

1. Select the cells that need to be reformatted with the correct date format.

2. From the Data tab select Text to Columns.

3. Follow the instruction for the version of Excel you are working in.

Change existing dates in Excel 365 Online

Remove the selection from all the Delimiter options and then click Apply.

Change existing dates in Excel 365 for Desktop

1. On Step 1 of the Convert Text to Columns Wizard select Delimited and then click Next.

2. On Step 2, leave all the delimiter options unchecked and then click Next.

3. On Step 3, select Date from the Column data format option box, and then select the format that the date is currently using. For example, if your date format is currently mm/dd/yy, and you want to change it to your default locale setting, select MDY. If it is dd/mm/yy and you want the dates to be formatted as American date format select DMY.

4. Click Finish.

Your dates will now be updated and the correct Date Formats reapplied.

Was this blog helpful? Let us know in the Comments below.