Are you working on your companys shared spreadsheet but cant sort your data by date?
Dont worry; its a common issue and not that hard to solve.
When users use different Excel versions to work on the same document, the date formation wont update currently.
So without wasting any further, lets fix the issue and get back to working with your data.
you could 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 controls.
Also, when youcopy and paste any data from another Excel sheet, the date wont format correctly.
So you should be more careful while copying the objects from other spreadsheets.
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.
In that case, use the Quick Format tool from theHomeribbon to solve the issue.
Because sometimes Excel cant recognize the date for region changes.
Why Does Your Date Formatting Not Work in Microsoft Excel?
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.
In that case, you gotta change the data formatting.
You will also face issues likeVLOOKUP not workingor theSUM formula not workingfor the wrong data formats in Microsoft Excel.
So be careful while inputting data in your spreadsheet.
So follow any single date format for each document.
Regional parameters
The regional parameters on your system can affect the date format in Microsoft Excel.
So change the dates of your Excel sheet according to your PC.
So check the formulas if you face any issues with the date.
You should alsoinstall Macro in Excelto automate the date-sorting process.
FAQs
Why does Excel keep changing the date 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’re gonna wanna change the date format to solve this issue.
Why is the date value not working?
The DATEVALUE function isnt 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.
After applying the fixes, Im sure You have fixed all your problems with date Formatting in MS Excel.
Comment if you face difficulties during the walkthrough.
