How-To

How to Fix Excel Not Recognizing Dates

By Adam Davidson

Published August 11, 2023

Is Excel not recognizing dates in your spreadsheet? There could be a few reasons why. To fix the problem, follow the steps in this guide.

Excel is an incredibly powerful spreadsheet application, but it can sometimes seem a little dumb. You’ve got data that’s clearly in a standard date format, but for some reason, Excel isn’t recognizing the cells as containing dates at all. For example, if you try to sort your Excel data by these dates, you don’t get the expected order.

The good news is that it is possible to jolt Excel into finally realizing your cells contain dates, and there are several ways you can go about it. Here’s how to fix Excel not recognizing dates.

How to Fix Date Recognition in Excel Using Text to Columns

One effective method of fixing problems with Excel not recognizing dates is to use the Text to Columns tool. This allows you to convert your data into a specific date format so that Excel recognizes it. You can then format the cell to display the date in the format that you wish.

To fix dates using the Text to Columns tool:

1. Select the column containing your dates by clicking the column header, or highlight the specific dates.
   
2. Click the Date menu.
   
3. In the ribbon, click the Text to Columns button.
   
4. Ensure that Delimited is selected and click Next.
   
5. Make sure none of the Delimiters are checked and click Next.
   
6. Select Date under Column Data Format and choose the format that your dates appear. In this example, the format is day/month/year so we would select DMY from the drop-down.
   
7. Click Finish.
8. Your cells should now be recognized as dates and converted to the default date format.
   
9. To set your desired date format, select the column again and right-click any cell.
10. Select Format Cells.
    
11. Choose Date as the Category.
    
12. Select your desired date format; you can change the locale if you need to switch the order of day and month.
    
13. Click OK and your dates convert to the selected format.
    
14. You can now sort your dates in chronological order correctly.

How to Strip Times From Your Dates in Excel

If your data has both a date and a time within the same cell, this may cause problems. You can use a similar method to the one above to strip away the times and just leave the dates. You can then format the dates as you see fit.

To strip times from your dates using the Text to Columns feature in Excel:

1. Select the column containing your dates and times.
2. Select the Date menu.
   
3. Click the Text to Columns button.
   
4. Select Delimited and click Next.
   
5. Choose the symbol that comes between your dates and your times in your data, such as a space, comma, or semicolon.
   
6. If it’s a different symbol, select Other and enter the symbol in the box.
7. In the Data Preview box, you should see your dates and times split into two different columns.
   
8. Click Next.
9. In the Data Preview select the column containing your dates.
   
10. Under Column Data Format select Date and choose the order that your dates appear. In this example, the order is day/month/year so we would select DMY from the drop-down.
    
11. Select the column containing your times in the Data Preview.
    
12. Select Do Not Import Column (Skip). This will ensure that the times are stripped from your data.
    
13. Click Finish.
14. Your cells should now contain only dates and no times.
    
15. To set the date format, highlight the column again and right-click any of the selected cells.
16. Click Format Cells.
    
17. Select Date.
    
18. Choose a date format from the options.
    

How to Convert Text to Values in Excel Using the VALUE Function

Another method that you can use to get Excel to recognize your dates is to use the VALUE function, which takes a text string and converts it into a value. If Excel is seeing your dates as text, this may fix your problems.

To convert text to values using the VALUE function in Excel:

1. Select an empty column.
2. In the first row that contains dates, type =VALUE(
   
   
3. Select the first date in your dates column.
   
4. Type ) and press Enter.
   
5. You should now see a numerical code that represents your date.
   
6. Right-click the cell and select Format Cells.
   
7. Select Date.
   
8. Choose a date format then click OK.
   
9. Your cell should now display a valid date.
10. To apply the formula to the other cells in the column, click and hold the drag handle in the corner of the cell, and drag down.
    

How to Convert Text to Date Values Using DATEVALUE in Excel

You can also use the DATEVALUE function to convert text into a valid date. This works much like the VALUE function.

To convert text to values using the DATEVALUE function:

1. Select an empty column where you can enter your formula.
2. In the row corresponding to the first date in your dates column, type =VALUE(
   
   
3. Select the first entry in your dates column.
   
4. Type ) and press Enter.
   
5. This will generate a numerical code that represents your date.
6. Right-click the cell and select Format Cells.
   
7. Select Date as the category.
   
8. Choose a date format and click OK.
   
9. Your cell should display a date.
10. Click and hold the drag handle in the corner of the cell, then drag it down to apply the formula to other cells.
    

Using Find and Replace to Fix Date Values in Excel

Excel will sometimes need a little nudge to make it recognize that your cells contain dates. One method that has been successful for many people is to use Find and Replace to replace a symbol in your data with itself. This seems to be enough to get Excel to re-evaluate the contents of the cell and realize that it contains a date.

To get Excel to recognize a date using Find and Replace:

1. Press Ctrl+H (or Cmd+H on Mac) to open the Find and Replace window.
2. In the Find What field, enter a symbol that appears in your dates, such as a forward slash (/) symbol.
   
3. In the Replace With field, enter the same symbol.
   
4. Click Replace All.
   
5. Hopefully, Excel will re-evaluate your cells and now recognize them as containing dates.

Using Excel’s Error Menu to Check for Date Errors

If your date is entered in an unusual format, Excel may recognize this as a potential error and flag it up with an exclamation symbol. By clicking this symbol you can see options to fix the potential error by changing the formatting of the date.

To fix a date using Excel error checking:

1. Look for a date with a small green triangle in the top left-hand corner. This indicates an error.
   
2. Select the cell and a warning symbol should appear.
   
3. Click the symbol to reveal a context menu.
4. Select Convert XX to 19XX to convert the years to four-digit years such as 1986.
   
5. Choose Convert XX to 20XX to convert the years to four-digit years such as 2023.
   
6. Your date should now reflect your chosen option with no error warnings.
   

Fixing Common Excel Problems

Knowing how to fix Excel not recognizing dates can bring an end to your frustration by ensuring that all of your dates are recognized and in the right formats.

There are, unfortunately, other common issues that can occur when using Excel. For example, there are a few things you can try if you’re having problems with VLOOKUP errors in Excel. You can also learn what to do if you’re having issues with Excel not dividing correctly. And if Excel isn’t auto-calculating correctly, there are a few things you can try that should help to fix it.