Are you working on your company’s shared spreadsheet but can’t sort your data by date? Don’t worry; it’s a common issue and not that hard to solve.
When users use different Excel versions to work on the same document, the date formation won’t update currently. To solve this particular problem, this article will guide you to fix the date format mm dd yyyy not working issue in Microsoft Excel.
So without wasting any further, let’s fix the issue and get back to working with your data.
How to Fix Date Formatting in Microsoft Excel
You can quickly solve the date formation in excel using the Text to Columns tools. You can also manually repair the incorrect data for each date cell. But the process is very lengthy. If you work on a shared spreadsheet, you must change your Regional Format Settings.
Also, when you copy and paste any data from another Excel sheet, the date won’t format correctly. So you should be more careful while copying the objects from other spreadsheets.
This section will show you how to fix your problem step by step which will make it easy for you to solve the issue.
Here are the methods to fix the date format in Microsoft Excel:
1. Use the Text to Columns Tool
The Text to Columns tool helps users to format the cells correctly. If Excel recognizes your date in any other format, you can use this tool to fix the issue.
To fix the date formatting using the Text to Columns tool, follow the steps below:
- Open the Excel sheet and select your date column.
- Navigate to the Data ribbon and select the Texts to columns option.
- Click on the Next> option two times.
- Select the Date option and choose the date format from the box.
- Tap Finish to save the changes.
After completing the steps, Microsoft excel won’t show #### instead of the date.
2. Use Quick Format
When you copy any date data from another file, Excel sometimes won’t identify them as dates. In that case, use the Quick Format tool from the Home ribbon to solve the issue.
Here are the steps to use the Quick Format option to fix the date issue:
- Select the date column on your Excel sheet.
- Go to the Number section of the Home ribbon.
- Choose the Numbers option in the box.
After completing the steps, the Excel AI will recognize the date correctly, and you can start your calculation without any problems.
3. Manual Correction
Suppose the data errors are relatively smaller, and you don’t want to use any functions to change the date formatting. In that case, you can manually re-input all the data in the date cells to solve the date formatting not working issue.
Follow the steps below to manually re-input date data:
- Go to the differently formatted date cells on your excel file.
- Double-click on the cells.
- Press the Enter key on your keyboard or re-insert the date.
However, I don’t recommend this method because it takes a lot of time to re-input all the data in an Excel sheet.
4. Change Regional Format Settings
If you work on your company-shared document and face date formatting issues, you must change your Regional Format settings. Because sometimes Excel can’t recognize the date for region changes.
Here are the steps to change your Regional Format Settings in MS Excel:
- Open your spreadsheet on Excel 365 (web version).
- Go to File > Options.
- Select the Regional format settings option.
- Choose your region from the box and tap Change.
After you apply all the fixes, all kinds of date formatting issues in MS Excel will go away for good.
Why Does Your Date Formatting Not Work in Microsoft Excel?
Usually, date formatting in Excel only works when your sheet has the correct cell format, text input instead of date option, or incorrect regional settings. Also, importing the date column in an unclear format or for a bug with the Excel function can cause this error.
And if you continuously insert formulas without fixing the issue, Microsoft Excel can freeze or slow. In this portion, I will show you the common reasons for date Formatting issues in Microsoft Excel. Let’s see.
These are some common causes for date Formatting issues in Microsoft Excel:
Incorrect Cell Data Type
Mainly you face the date formatting issue when Microsoft Excel recognizes the date values of your sheet as General or Text rather than Date or Date & Time. In that case, you need to change the data formatting.
You will also face issues like VLOOKUP not working or the SUM formula not working for the wrong data formats in Microsoft Excel. So be careful while inputting data in your spreadsheet.
Incorrect Date Format
Microsoft Excel won’t recognize the date if you select two different date formats in a single column. So follow any single date format for each document.
Regional Settings
The regional settings on your computer can affect the date format in Microsoft Excel. So change the dates of your Excel sheet according to your PC.
Incorrect Formula
The date format will only display results if you insert the correct formulas in the function bar.
So check the formulas if you face any issues with the date. You should also install Macro in Excel to automate the date-sorting process.
FAQs
Why does Excel keep changing the date to 1 0 1900?
Microsoft Excel can not recognize the data as a date when the date value changes from 1/0/1900 to 1 0 1900. You should change your cell formatting to fix the issue.
Why does Excel continuously change my year to 1905?
It happens when you set your date formation in (yyyy) instead of (dd.mm.yyyy). You need to change the date format to solve this issue.
Why is the date value not working?
The DATEVALUE function isn’t working because your dates are already in the date format. And if you try the DATEVALUE function, you will get a #VALUE error in your worksheet.
Conclusion
Long story short, sorting data is helpful if you manage a large spreadsheet. But issues happen when different people use different Excel apps to work on the same document. You can solve it using the Text to Columns tools, Quick Format tool, or manually repair the incorrect data.
After applying the fixes, I’m sure You have fixed all your problems with date Formatting in MS Excel. Comment if you face difficulties during the walkthrough.
Peace!
