Excel Online isn't detecting a date being entered as a date. (Bug?)

Question

We have a spreadsheet with no fancy formulas. It consists of some dates entered in DD/MM/YY format. Every day, staff will add new rows to the data and then click sort by A-Z, and it has been working fine. However, recently when arranging the rows A-Z on the date, the new rows always stay at the bottom as if Excel doesn't understand that it is a date. If we type the date in as DD/MM/YYYY format, it doesn't automatically change to DD/MM/YY. 

We've selected the date columns and made sure it knows it is a date format in DD/MM/YY format, but still it doesn't understand it is a date. If we change it to DD/MM/YYYY format, and then back to DD/MM/YY format, it works, but not for new rows. It also works if we copy and paste the row into Notepad, and then copy and paste it back into Excel online, it suddenly understands it is a date and allows it to be sorted. 

If we save the document and open it into Excel 2016 locally, it gives some strange errors to say that the cell is a 2 digit year which is actually correct, but Excel doesn't seem to understand that that is exactly what it should be.

This is after entering the date and clicking "Sort Ascending" (notice the bottom one doesn't sort):

This is the formatting of the cell (and the rest of the column):

This is when I save and open the document in Excel 2016 (The date cell reports an error about the 2 digit date even though it's fine):

Any idea's why this is happeneing?

Answer 1

After hours of this, we found the bug is that Excel Online keeps trying to switch the dates to the US format (MM/DD/YY) even though it's set to the UK layout. How can we report this?

Answer 2

Hi,

We need additional details to address your concern with date formatting in an Excel file. Kindly answer the following:

• Which Windows 10 build are you using? Click here for instructions on how to find your Windows 10 build.
• Is your Excel Online a part of an Office 365 subscription? If yes, which edition?
• Is it happening to all browsers?
• Is it happening to a specific file?

In the meantime, make sure that your account is set to the correct region by following these steps:

1. Go to OneDrive.com.
2. Click the OneDrive Settings.

3. Select Language.

4. Click Save.

We also suggest checking the service status in the Service Health Portal.

We will be waiting for your response.

Answer 3

Hi Kristine, 

The issue is when we use Excel Online, we can replicate the issue from any PC and from any Browser. We've used Edge, Chrome and Firefox but the issue is the same. 

In fact, we've found that this has been mentioned, and even submitted as a feature request in the past. 

You can see the issue too, if you open Excel Online, and type a date in the UK format in the first field (DD/MM/YY), it won't accept 20/02/2018 for example as it doesn't like there being 20 for the month. We've checked the region and language settings in every place we can find them, including on the local PC, it isn't an issue there. 

Excel Online simply doesn't allow dates to be anything other than the US format. There is no option to change the format to DD/MM/YYYY in cell formatting or anywhere. 

The only ways around this are to give into using the US format, or opening the document in Excel on your PC to edit the document where it will allow you to use all other formats for the date. 

Thanks,

Joe

Answer 4

Hello,

Just to ask, is your computer connected to a domain?

To further isolate this issue, we suggest recreating the Excel file and checking if you will still experience the same issue.

Please post back with the result for further assistance.