How-To

How to Fix Excel Not Recognizing Dates

excel not recognizing dates

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.
    excel column header
  2. Click the Date menu.
    excel data menu
  3. In the ribbon, click the Text to Columns button.
    excel text to columns button
  4. Ensure that Delimited is selected and click Next.
    excel delimited
  5. Make sure none of the Delimiters are checked and click Next.
    excel no delimiters selected
  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.
    excel dates format
  7. Click Finish.
  8. Your cells should now be recognized as dates and converted to the default date format.
    excel converted dates
  9. To set your desired date format, select the column again and right-click any cell.
  10. Select Format Cells.
    excel format cells
  11. Choose Date as the Category.
    excel date formats
  12. Select your desired date format; you can change the locale if you need to switch the order of day and month.
    excel select date format
  13. Click OK and your dates convert to the selected format.
    excel formatted dates
  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.
    excel data menu
  3. Click the Text to Columns button.
    excel text to columns button
  4. Select Delimited and click Next.
    excel delimited
  5. Choose the symbol that comes between your dates and your times in your data, such as a space, comma, or semicolon.
    excel delimiter
  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.
    excel delimited columns
  8. Click Next.
  9. In the Data Preview select the column containing your dates.
    excel dates column
  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.
    excel date format
  11. Select the column containing your times in the Data Preview.
    excel times column
  12. Select Do Not Import Column (Skip). This will ensure that the times are stripped from your data.
    excel do not import
  13. Click Finish.
  14. Your cells should now contain only dates and no times.
    excel dates with times stripped
  15. To set the date format, highlight the column again and right-click any of the selected cells.
  16. Click Format Cells.
    excel format cells
  17. Select Date.
    excel date formats
  18. Choose a date format from the options.
    excel select date format

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(
    excel value function
  3. Select the first date in your dates column.
    excel select cell
  4. Type ) and press Enter.
    excel value formula
  5. You should now see a numerical code that represents your date.
    excel date code
  6. Right-click the cell and select Format Cells.
    excel format cells
  7. Select Date.
    excel date formats
  8. Choose a date format then click OK.
    excel select date format
  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.
    excel drag handle

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(
    excel datevalue formula
  3. Select the first entry in your dates column.
    excel select cell
  4. Type ) and press Enter.
    excel datevalue formula
  5. This will generate a numerical code that represents your date.
  6. Right-click the cell and select Format Cells.
    excel format cells
  7. Select Date as the category.
    excel date formats
  8. Choose a date format and click OK.
    excel select date format
  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.
    excel drag handle

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.
    excel find what
  3. In the Replace With field, enter the same symbol.
    excel replace with
  4. Click Replace All.
    excel 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.
    excel error triangle
  2. Select the cell and a warning symbol should appear.
    excel warning symbol
  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.
    excel convert to 1900
  5. Choose Convert XX to 20XX to convert the years to four-digit years such as 2023.
    excel convert to 2000
  6. Your date should now reflect your chosen option with no error warnings.
    excel converted date

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.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top