MS Excel: How To Convert Text To Date In Excel

Here, are multiple ways to convert Text to Date in Excel

1. Convert Text to a Date Using Excel Error Checking

Excel provides an error-checking option in a worksheet that is used to alert Excel user about the presence of cells that contains text representations of date with the 2-digit years format.

This can identify by a small colored triangle (i.e. error indicator) in the cell of the top left corner. If the cells in your Excel worksheet indicate this colored triangle in the cell then you can use Excel error checking to change the representation of the text to date into a numeric date.

Do the below-mentioned steps:

• Select cell or cells of the Excel worksheet to convert texts to dates (This will bring a warning symbol at the right side of the cell and if you hover the cursor on that symbol a message will be displayed).
• Now, click on the warning of the cell to open the options and choose one of the options from the list (Convert XX to 19XX, or Convert XX to 20XX).
• This will help to convert a cell text value to the dates.

Convert Text To A Date Using Excel Text To Columns

Excel text of cells can be covert to dates using Text To Columns. The benefit of using this method is, this method can recognize many different formats of dates. Text To Columns method will work only for one column at a single time.

Follow the steps to convert text to dates using Text To Column method:

• Select a cell in the Excel worksheet that you want to convert but select a single column only.
• Go to Data option at the top.
• Now, Select the Text To Columns option. A pop-up window will open, within the Convert Text to Columns wizard:
  • Delimited option should be clicked and click on the next button.
  • Deselect all the delimiters and click again on the next button.
  • In Column data format options click on Date option.
  • Drop down is present at the right side of the Date option, you can select a format of date of your choice.
  • Click on the Finish button, to apply the changes.

Convert Text To A Date Using The Excel Datevalue Function

Choose cells A1 to A3 of the Excel worksheet and put the dates as, 01/01/2023, 01/01/23 and 01 Jan 2023(3 different text representations of date).

Now, using the DATAVALUE function you can convert this text to date in very simple steps.

Make sure column B should be formatted with General type of format. Now give an integer value like 42370 in the B1 cell.

Now, you have to change the format type of the cell to the Date format if you want to show this integer value in a date format. This is the normal procedure but this can be done in the easiest way.

The simplest way is, to enter an integer value in an empty cell then go to the drop-down list as shown in the image and select the type as Date, and you are done.