Resolve Excel Not Recognizing Date Format- Time-Saving Tricks

Microsoft Excel is an exceptional tool used to manage or analyze data. But sometimes, it happens that Excel not recognizing date format DD/MM/YYYY. Many users have reported the same issue where Excel fails to identify dates and difficulties in their spreadsheets.

Well, in this blog, I will discuss the common reasons behind this problem and other significant info regarding Excel not recognizing dates along with effective ways to troubleshoot it.

Quick Tricks:

1. Use Text To Columns
2. Dates As Text
3. Convert Date Into Numbers
4. Format The Dates
5. Use The VALUE Function
6. Using DATEVALUE Function
7. Find & Replace
8. Run Error Checking

When Excel Not Recognizing Dates Properly?

Excel may not identify the date format:

• When you copy or import data into Excel all your date formats go wrong.
• Excel recognizes the wrong dates and all the days and months get switched.
• While working with the Excel file which is exported through the report. You may find that changing the date value cell format into a date is returning the wrong data.

Why Excel Doesn’t Recognize Date?

This problematic situation can happen anytime and with anyone so you need to be prepared to fix this issue smartly. But before addressing it, you should extract the root causes of the problem. So, let’s take a look:

1. When your source file or from where you are importing the data follows the mm/dd/yy instead of the dd/mm/yy date format.
2. Due to formatting inconsistencies in the spreadsheet.
3. When trying to sort the dates column, all the data gets arranged in the incorrect order which leads to this issue.

How to Fix Excel Not Recognizing Date Format?

Resolving the date not changing in Excel issue is not a tough task but for that, you need to know the right solution. Here are the fixes that you need to apply to fix this problem.

Trick 1- Use Text To Columns

Here are the fixes that you need to perform.

• Firstly you need to highlight the cells having the dates. If you want then you select the complete column.
• Now from the Excel ribbon, tap to the Data menu and choose the ‘Text to columns’ option.

• In the opened dialog box choose the option of ‘Fixed width’ and then hit the Next button.

• If there are vertical lines with arrows present which are called column break lines. Then immediately go through the data section and make a double click on these for removing all of these. After that hit the Next button.
• Now in the ‘Column data format’ sections hit the date dropdown and choose the MDY or whatever date format you want to apply.
• Hit the Finish button.

Very soon, your Excel will read out all the imported MDY dates and then convert it to the DMY format. All your problems with excel not recognizing date format dd/mm/yyyy have now been fixed in just a few seconds.

Also Read: Fix Excel Flash Fill Not Recognizing Pattern (5 Quick Ways)

Trick 2- Dates As Text

In the shown figure, you can see there is an imported dates column in which both data and time are showing. If in case you don’t want to show the time and for this, you have set the format of the column as Short Date. But nothing happened date format not changing in Excel.

Now the question arises why Excel date format is not changing?

 Mainly this happens because Excel is considering these dates as text. However, Excel won’t apply number formatting in the text.

Having doubt whether your date cell content is also counted as text then check out these signs to clear your doubt.

• If it is counted as text then Dates will appear left-aligned.
• You will see an apostrophe in the very beginning of the date.
• When you select more than one dates the Quick Calc which present in the Status Bar will only show you the Count, not the Numerical sum or Count.

Trick 3- Convert Date Into Numbers

Once you identify that your date is a text format, it’s time to change the date into numbers to resolve the issue. That’s how Excel will store only the valid dates.

For such a task, the best solution is to use the Text to Columns feature of Excel. Here is how it is to be used.

• Choose the cells first in which your dates are present.
• From the Excel Ribbon, tap the Data.
• Hit the Text to Columns option.

• In the first step, you need to choose the Delimited option, and click the Next.

• In the second step, from the delimiter section chooses Space In the below-shown preview pane you can see the dates divided into columns.
• Click the Next.

In the third step set the data type of each column:

• Now in the preview pane, hit the date column and choose the Date option.
• From the Date drop-down, you have to select the date format in which your dates will be displayed.

Suppose, the dates are appearing in month/day/year format then choose the MDY.

• After that for each remaining column, you have to choose the option “Do not import column (skip)”.
• Tap the Finish option for converting the text format dates into real dates.

Trick 4- Format The Dates to Fix Excel Not Recognizing Date Format

After the conversion of the text dates or real dates, it’s time to format them using the Number Format commands.

Here are a few signs that help you to recognize the real dates easily:

• If it is counted as text then Dates will appear right-aligned.
• You will see no apostrophe at the very beginning of the date.
• When you select more than one date the Quick Calc which is present in the Status Bar will only show you the Count, Numerical count, and the sum.

For the formatting of the dates, from the Excel ribbon choose the Number formats.

Everything will work smoothly now, after converting text to real dates.

Also Read: Excel AutoRecover Not Working? Here’s How to Fix It!

Trick 5- Use The VALUE Function

The VALUE function is mainly used for compatibility with other workbook programs. Through this, you can easily convert the text string which looks like a number into a real number. Overall it’s helpful for fixing any type of number not only the dates.

Here is the Syntax which you need to use:

Where ‘text’ is referencing the cell having the date text string.

Notes:

1. If the text string which you are using is not in the format that is well recognized by Excel then it will surely give you the #VALUE! Error.
2. For your date, Excel will return the serial numbers, so you need to set the cell format as the date for making the serial number appear just like a date.

Trick 6- Using DATEVALUE Function

DATEVALUE is very much similar to the VALUE function. The only difference is that it can convert the text string which appears like a date into the date serial number.

With the VALUE function, you can also convert a cell having the date and time which is formatted as text. Whereas, the DATEVALUE function, ignores the time section of the text string.

Here is the Syntax which you need to use:

Where ‘date_text’ is referencing the cell having the date text string.

Trick 7- How to Solve Excel Not Recognizing Dates Using Find & Replace?

In your dates, if the decimal is used as delimiters like this, 1.01.2014 then the DATEVALUE and VALUE are of no use

In such cases using Excel Find & Replace is the best option. Using the Find and Replace you can easily replace decimal with the forward slash. This will convert the text string into an Excel serial number.

Here are the steps to use find & replace:

1. Choose the file in which you are getting the issue.
2. From your keyboard press CTRL+H This will open the find and replace dialog box on your screen.
3. Now in the ‘Find what’ field put a decimal, and in the ‘replace’ field put a forward slash.
4. Tap the ‘Replace All’ option.

Excel can now easily identify that your text is in number and thus automatically format it like a date.

HELPFUL ARTICLE: Fix Excel Find And Replace Not Working Issue

Trick 8- Run Error Checking

Last but not least option that is left to fix Excel not recognizing dates is using the inbuilt error-checking option of Excel. This inbuilt tool always looks for the numbers formatted as text.

You can easily identify if there is an error, as it puts a small green triangle sign in the top left corner of the cell. When you select this cell you will see an exclamation mark across it.

By hitting this exclamation mark you will get some more options related to your text:

In the above-shown example, the year in the date is only showing 2 digits. Thus Excel is asking in what format I need to convert the 19XX or 20XX.

You can easily fix all the listed dates with this error-checking option. For this, you need to choose all your cells having date text strings right before hitting the Exclamation mark on your first selected cell.

Steps to turn on error checking option:

• Hit the office icon present in the top left corner of the opened Excel workbook. Now go to the Excel Options > Formulas
• From the error checking section choose “enable background error checking” option.
• Now from the error checking rules choose the “cells containing years represented as 2 digits” and “number formatted as text or preceded by an apostrophe”.

Use Excel Repair Software to Solve Errors & Issues in Excel

If you face any error or issue in Excel due to file corruption, then you can go for the first-rated Excel Repair Tool. This program is capable of fixing severely corrupted Excel files and restoring all the tables, charts, images, cell comments, formulas, filters, and sorts from that Excel workbook.

Apart from that, you can download the free demo version of this tool to scan & preview recoverable data from a file.

Try Now       Buy Now

* Free version of the product only previews recoverable data.

Related FAQs:

Can I Set a Default Date Format for All Workbooks in Excel?

Yes, you can easily set the default date format for all workbooks in MS Excel by navigating to ‘File’ menu >> ‘Options’ >> choose ‘Advanced’. In the ‘General’ tab, you can set your chosen date format as the default.

Why Does Excel Sometimes Convert Dates to A Different Format Automatically?

Excel might convert dates to a different format automatically if it detects data inconsistencies or if the regional settings don’t match the date format used.

What Should I Do If Excel Changes the Date Format Automatically When I Enter Dates?

If your Excel changes the date format automatically when you enter dates, it might be because of incorrect regional settings. To tackle the situation, you have to go to the File tab >> Options >> Advanced option. In the Editing Options, simply uncheck the option ‘Automatically insert a decimal point’.

Are There Any Shortcuts to Change Date Formats In Excel?

Yes, there is shortcuts to change date formats in MS Excel. Simply, press on ‘Ctrl+1’ keys together to open a Format Cells dialogbox, then go to ‘Number’ tab >> select ‘Date’. Select the wanted format from that list.

Can I Convert Text Dates to Proper Excel Date Values?

Yes, you can use DATEVALUE function to convert text dates to proper Excel date values.

How Can I Prevent Date Format Issues When Importing Data into Excel?

To prevent date format problems when importing data into Excel spreadsheet, ensure to format the dates constantly in the source data.

Final Thoughts

That’s it! I hope the outlined workarounds have helped you resolve Excel not recognizing the date format issue. All you need to do is to try them accordingly to deal with the situation.

If you’ve any questions related to this topic, feel free to ask us on Facebook and Twitter.