As we all know that Microsoft Excel is a very helpful and popular workbook for users to store any kind of important data safely.
In Microsoft Excel users stores various types of valuable, confidential, official, projects, and important data in a sequence order with dates as per their convenience. Along with dates, numbers and times are also the most common data types that people use in Excel.
But sometimes if you want to change the date of your Excel document, you are unable to change the date due to unexpected errors.
Therefore, today here I am with my new blog post discussing date format not changing in Excel. Don’t worry; I will discuss each and everything in detail about why date format does not change in Excel.
So, let us go through this blog carefully to solve the issue of Excel unable to change date format.
Excel date formatting not working
I have an Excel sheet created by a 3rd party program.
One of the columns has dates in this format: “Jan 19, 2015, 03:00:00 PM”
I would like these dates to appear in the following format: “19/01/2015”
I have selected the cell or cells, right-clicked and selected “Format Cells…” chose “Date” in the category, then chose “14/03/2001” in the type, to no avail, the dates won’t change.
I also tried “Custom” from the category and “dd/mm/yyyy” from the type, again, no changes at all.
The file is not protected, the sheet is editable.
Could someone explain what I could be doing wrong?
To recover lost Excel data, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
Why Is My Date Formula Not Working In Excel?
There may certain causes or unwanted reasons that date formula not working in Excel properly. But before we get into the solutions of date format Excel not working issue, let’s put a spotlight on some of the causes for why is my date formula not working in Excel.
1) Cells are formatted as text
Cells are formatted as text means that the formula will appear directly in the cells instead of text. This is due to when you import any data from notepad or CSV file.
2) The workbook is to be set in Manual calculation mode
If you are doing any type of calculation work in Excel by copying and pasting the formula, then the result will be copied in place of the right answer. This will appear until you set the workbook in manual calculation mode.
How To Fix Date Format Not Changing In Excel?
As we all know that users like to save their Excel files as per the dates in manual order for convenience in use. People can also change the dates whenever required as per their need but sometimes due to unwanted errors date format does not change in Excel.
So, there are different ways to fix the issue of date format does not change in Excel and they are as follows:
Method #1: Sorting The Date
To fix Excel unable to change date format issue, just follow the steps given below.
- At first, press the Window + R button to open the Run dialog box
- Then type Regedit and then press the OK button
- Now, select the HKEY_CURRENT_USER option
- Here choose the Control Panel menu.
- After that choose the International option.
- Now, double click on sShortDate menu.
- Another window will open the screen.
- Here, change the format of the date that you want to change.
Method #2: Format The Cells
In Microsoft Excel, dates can be displayed in a variety of ways. To solve date format Excel not working issue, go through the below steps:
- Open your Excel worksheet
- Now, right-click on the top of the Excel column and select the Format Cells menu
- After selecting the Format Cells, a format cells Window will appear
- Here, choose the Date option and select the date format that you needed for that particular column
- Finally, click on the OK button to save the format change and then close the Window
- Thus, by following this step you will able to solve your date format problem
Method #3: Resize The Cells Size
Generally, MS Excel understands the dates exceptionally fine and it is improbable to hit any barrier when working with them.
If still, you are facing date format Excel not working issue, then just follow the troubleshooting tips provided to you to get rid of date format not changing in Excel problem.
If the cell is not having enough space to fit the complete date and you observe a number of pound signs (#####) in place of the dates in your Excel spreadsheet, then the cells are not broad to the completion dates that you enter.
Now to auto fix the dates, double click on the right border of the column to resize it. Or else, to set the column width; just drag it to the right border to fix the date format Excel not working issue.
So, this method is applicable for date format not changing in Excel 2007, 2013, 2016.
Method #4: Change The Date To Number Format
To fix the issue of Excel unable to change date format to real dates, with the help of Number format commands you can format them.
To perform this, follow the steps given below:
- Go to your Excel document
- Select the Dates that you want to format
- Now, go to the Excel Ribbon menu
- Select the Number Format option
- Or else click on the dialog launcher option to see more format options
- After that convert, the text dates to real dates and see that everything is working properly or not
Recommended Solution: MS Excel Repair & Recovery Tool
While performing the steps to solve the date format Excel not working issues in different Excel files such as XLS, XLSX, XLT, XLS, XLSM, XLSX, sometimes due to unwanted causes or circumstances your files get damaged or deleted.
So in that scenario, we have a professional recommended tool i.e. MS Excel Repair & Recovery Tool to repair all types of Excel files like XLS, XLSX, XLT, XLS, XLSM, XLSX with ease.
With the help of this software, you can restore all types of Excel files such as tables, columns, formulas, dates, charts, images and many more.
As well as you can also fix all types of MS Excel corruption errors and it is used in all the versions of MS Excel which includes Excel 2000, 2003, 2007, 2010, 2016 & 2019.
Steps to Utilize MS Excel Repair & Recovery Tool:
Hope the above-specified solutions suggested to you will help you to solve the issue of Date format not changing in Excel.
I tried my level best to bring simple and effective ways to fix why is my date format not changing in Excel problems.
Thus, make proper use of the methods provided to you and in the future also if you come across such types of issues then you can easily get rid of this with ease.