How to convert text to date in Excel

With Ultimate Suite for Excel

The Text to Date tool quickly converts text strings that look like dates into true Excel dates so you can sort, filter, or use them in formulas and PivotTables afterwards. Read this guide to know how to work with Text to Date in Excel.

Before you start

There are some things you should keep in mind before using the tool:

  • The format of converted dates depends on the regional date and time settings adjusted in your system. Besides, the add-in can adapt months and weekdays typed in a language other than English only if you have it set as a preferred Office display or Office authoring language in Excel Options.
  • The data you'd like to convert should be arranged in a column. The add-in can process only one column with text dates at a time.

How to turn text strings into dates

To run the tool, go to the Date & Time section under the Ablebits Tools tab and click the add-in icon:
Start Text to Date.
The Text to Date window will appear with a few options to choose from:
Choose Text to Date options.

  1. The range with the entries to convert is displayed in the Select range field.
  2. Click the Expand selection icon on the right to have the entire column with the data highlighted.
    To alter the range, enter the needed address manually or click the rightmost icon in the field. In the latter case, the Select Range dialog box will open allowing you to pick the necessary cells:
    Select the necessary range.
  3. Check the My table has 1 header row box if your table has a header that you do not want to be processed during the conversion. If there are more rows with labels in your table, click on the "1 header row" phrase in blue and enter the number of rows that should be excluded:
    Click 1 header row.
    However, if the header has been detected incorrectly and is absent in the selection, clear this box:
    Clear the May table has a header box.
  4. The add-in automatically identifies the order of date units based on a value in the topmost cell of the selected column. You can switch to another option that corresponds more to your data if needed. For example, if your text dates look like 02-19-88 or 021988, select the Month Day Year option.
    Tip. If a year is presented as 4 digits (1988), its position in the original dates makes no difference. You can pick either option with Year at the end or at the beginning paying attention just to the order of Month and Day. For example, if your text dates look like 1988-02-19 and 02-19-1988, the first and fourth options will work the same for you:
    Specify the order of date units.
    Note. If a year is omitted in the original dates, the current year from your computer system is taken for the conversion.
  5. In case the selected text entries consist of a date and time, choose the Exclude time option to get only the dates converted:
    Check the Exclude time option.
    To keep both the date and time, have this option unchecked. The outcome will be as follows:
    Uncheck the Exclude time option.
  6. Tick off the Mark not converted cells option to highlight unchanged cells in red.
    Mark not converted cells.

Click Convert and see the result inserted to the right of your original column.