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 group on the Ablebits Tools tab and click Text to Date:
Start Text to Date.
The Text to Date pane appears:
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 selected automatically.

    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 range:
    Select the necessary range.

  3. Select the My table has 1 header row checkbox if your table has a header that you don't want to be processed during the conversion. If there are more header rows in your table, click 1 header row 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 from the selection, clear this checkbox:
    Clear this checkbox.
  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. Select Save the selected date order as default setting if you want to use the chosen date order when working with the add-in later on.
  6. In case the selected text entries consist of a date and time, choose the Exclude time option to get only dates converted:
    Check the Exclude time option.
    To keep both date and time, clear the Exclude time checkbox. The result will be as follows:
    Uncheck the Exclude time option.
  7. Select the Mark not converted cells checkbox to highlight unchanged cells in red.
    Mark not converted cells.

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