3 Ways To Fix Excel Not Recognizing Date Format Issue

Are you getting the data format not recognizing issue in Excel Application? Looking for some quick fixes to resolve Excel not recognizing date format problem?

If yes then scroll down to this post to get some best fixes to resolve Excel not recognizing date format issue.

Let’s get started…!

When Excel Not Recognizing Date Format?

• Meanwhile importing or copying data in the Excel. Due to which, the whole formatting gets messed up.

• Excel recognizes incorrect dates and days/month gets switched.

• When you are working on the Excel file that gets exported through report. You need to find after modifying date value cell format into the date which is returning incorrect data.

Why Excel Not Recognizing Date Format?

Your PC is identifying dates in the dd/mm/yy format. Whereas, your source file follow the mm/dd/yy date format.

The second reason is that when trying to sort dates column, entire data gets arranged in the wrong order.

You may also like:

Fix ‘Excel Application Was Unable To Start Correctly’ Error [7 Best Ways]

How To Fix Excel Not Recognizing Date Format?

Following are the fixes to resolve Excel not recognizing date format.

1# Text To Columns

Here are the fixes you need to do:

• At first, highlight the date’s cell. If you wish you can choose the entire column.

• Hit the Data menu > 'Text to columns' option from the Excel ribbon.

• Now in the opened convert text to column wizard dialog box, select the 'Fixed width' option and then tap the Next button.

• If vertical lines with arrow appears which is also known as column break lines. In that case immediately go to the data section and make double click on this for removing it. After that tap on the next option.

• From the 'Column data format' sections you have to hit the date dropdown. From the dropdown list hit the MDY or whatever date you need to apply.

• Tao to the Finish button.

You will that your excel file will read out all the imported MDY dates after that convert it in to DMY format. Hopefully you won’t get Excel not recognizing date format error in just few seconds.

Also read: How To Fix Excel Track Changes Grayed Out Issue?

2# Convert Date Into Numbers

Once you recognize your date is in the text format, it’s time to modify date into numbers to resolve issue. That is how Excel stores only valid dates.

For such cases, the best option is to use Excel text to Columns feature. Follow the steps to use this.

• Choose Excel cells first having the dates.

• From the Excel Ribbon, just tap the Data tab.

• After that tap the Text to Columns option.

• In the first step, just choose the Delimited option, and hit the Next button.

• Now in the second step, go to the delimiter section and choose the Space option. Now in the below shown data preview pane you can see here that the dates are divided into columns.

• Tap to the Next option.

• Now in the preview pane, you have to hit the date column and then choose the Date option.

• From the Date drop-down, just choose the date format in which the date is displayed.

Suppose, your dates are in the format of month/day/year then it means you have to choose the MDY format.

• Now for each left out column, you need to choose “Do not import column (skip)” option.

• Tap to the Finish button for converting date with text format into the real one.

3# Use The VALUE Function

Following Syntax should be used for VALUE Function

=VALUE (text)

In this syntax ‘text’ is replaced with the cell saved with date text string.

This value function is specifically used for compatibility with other workbook programs. Using this, one can easily change the text string into real numbers. Apart from this the function also works great in fixing up, any type of number issue.

Notes:

1. If text string is not in right format then excel will definitely throw the #VALUE! Error.

2. Excel return serial number for date so to avoid this. You need to apply the cell format as date as this will make the serial number display like date.

Wrap Up:

Do try all the fixes I have shared in this post. It will definitely resolve your Excel not recognizing date format issue.