How to convert text to date in Excel? 9 easy ways! (2023)

Calculating the number of days between two dates, sorting and filtering data based on dates, and applying a date format are some of the basic functions we commonly do in Excel.

However, these operations are difficult or impossible to perform if the date information is stored as text strings.

Therefore, date information entered as text strings in Excel must be converted to valid dates if we want to use them in date calculations and other date-related operations.

This tutorial shows nine techniques forconvert text to date in excel.

Understanding the concept of date in Excel

In Excel, dates are consecutive numbers that represent individual days, months, and years since January 1, 1900. For example, the serial number for October 15, 2024 is 45580.

This number means that 45,580 days have passed since January 1, 1990.

We can apply different date formats to the numbers that represent dates to display them as normal dates in a human-readable format.

Because Excel stores dates as consecutive numbers, you can perform mathematical operations on dates, such as finding the difference between two dates or adding a specific number of days to a date.

However, when we enter date information in Excel as text, Excel does not recognize them as dates, but as text strings, which cannot be used in date calculations or other date-related operations.

This tutorial shows you how to convert two formats of text strings to dates in Excel:

• Text strings that have Excel-compatible date formats.
• Text strings with date formats not supported by Excel.

Convert text to dates in Excel (for Excel compatible text date format)

Excel supports a variety of date formats that display dates in different ways.

For example, the date format "mm-dd-yyy" displays the value 44965 as 2023-08-02.

When we enter a date in an Excel-compatible date format, Excel recognizes the input as a date and internally stores a serial number representing the date.

For example, when we type 02-08-2023, Excel recognizes this value as a date and stores the serial number 44965 internally, displaying it in the cell as 02-08-2023.

However, sometimes data entered into Excel, even if it is in Excel-compatible formats, may not be recognized by Excel as valid dates if it is in text format.

For example, the date 2/8/2023 is in an Excel-compatible date format and looks like a valid date, but if formatted as text, Excel will not recognize it as a valid date.

Therefore, we use the following methods to convert such data into valid dates recognized by Excel.

Method #1 – Use the DATEVALUE function to convert text to a date in Excel

HeFECHAVALOThe function converts a date from a text string to a number representing a date.

So we can use the function to convert text with Excel-compatible date formats to serial numbers that represent dates.

Consider the following data set imported into Excel from a website.

Contains the date data in column A as text strings that have date formats supported by Excel.

Note that the data is left-aligned, which means Excel recognizes it as text, not dates, even though they look like dates.

We want to use the DATEVALUE function to convert text strings to numbers that represent dates and display the numbers in column B.

We use the following steps:

1. Select cell B2 and enter the following formula:

=VALORFECHA(A2)

2. Press Enter on your keyboard or click Enter in the formula bar.

3. Drag or double-click the fill handle to copy the formula to the column.

The numbers representing the dates appear in column B.

In the following steps, we apply a date format to the numbers in column B so that they appear as human-readable dates.

4. Select the cell range B2:B2 that contains the numbers that represent the dates.

5. Ofdomtab insideNumbergroup, opennumerical formdrop-down menu and select one of thema short datelubricanta long date. In this case, we choose Long Date.

We convert the text to a date in Excel by displaying the dates in column B.

Use the Format Cells dialog for more control over using date formats

Stand outcell formattingA dialog box is a tool that allows us to change the appearance of cells and perform other formatting operations, such as applying date formats.

we can usecell formattingdialog box if you want more control over applying date formats to the numbers returned by the DATEVALUE function.

We can access the Format Cells dialog by pressingControl + 1.

So we can use itDatacategory options inNumbertab to apply various built-in zip formats or useCustomcategory options to apply custom date formats.

Method #2 – Use the VALUE function to convert text to a date in Excel

HePROWESSThe function converts a text string representing a number to a number.

For example, we can use this function to convert text strings in Excel-compatible date formats to numbers that represent dates.

Let's say we have the following set of data imported into Excel from a website. Contains the date data in column A as text strings in date formats recognized by Excel.

The data is left-aligned because Excel recognizes it as text and not as dates, even though they look like dates.

We want to use the VALUE function to convert the date strings in column A to numbers representing dates and display them in column B.

We proceed as follows:

1. Select cell B2 and enter the following formula:

=VALOR(A2)

2. Press Enter on your keyboard or click Enter in the formula bar.

Double-click or drag the fill handle to copy the formula down the column.

Numeric values ​​representing dates appear in column B.

We follow these steps to apply the date format to the numbers in column B.

4. Select the cell range B2:B6.

5. Ofdomeyelash wNumbergroup, opennumerical formdrop-down list and select onea short datelubricanta long date. In this case, we choose a short date.

We converted the text in column A to regular dates in column B.

If we want full control over date formatting, we can use the Format Cells dialog box as explained in method #1.

Method #3 – Use Numeric Operators to Convert Text to Date in Excel

In Excel, there are five arithmetic operators that we can use to perform basic math operations: the addition (+), multiplication (*), subtraction (-), division (/), and exponentiation (^) operators.

For example, we can use these operators to convert text strings in date formats accepted by Excel to numbers that represent dates.

Assuming that a text string with an acceptable date format is in cell A2, we can use any of the following formulas to convert the text string to a number representing a date, ensuring that the date is not tampered with in the process. calculation:

• =A2+0Adding zero to any number does not change the value of that number.
• =A2*1 Multiplying any number by one does not change the value of that number.
• =–A2 Preceding a number with double subtraction is equivalent to multiplying the number by a negative 1 (-1) followed by another negative 1 (-1). The first subtraction converts the text string to a negative number, and the second subtraction changes the negative number to a positive number representing the date.
• =A2/1 Dividing a number by one does not change the value of the number.
• =A^1 Raising a number to the power of 1 does not change the value of that number.

Let's apply the formulas to the following data set to convert the text strings in column A to regular dates in columns B, C, D, E, and F.

We apply the following steps:

1. Select cell B2 and enter the following formula:

=A2+0

2. Click Enter in the formula bar and drag or double-click the fill handle to copy the formula into the column.

3. Repeat steps 1-3 for columns C, D, E, and F, remembering to use the correct numeric operator in the header row.

The formulas gave the same results. Hence, we can use any of the five arithmetic operators based on preference.

Let's apply the date format to the numbers in column B by doing the following:

4. Select the cell range B2:B6.

5. Press Ctrl + 1 and this will open the Format Cells dialog.
6. clickNumbertab, select Date in the Category list box, scroll downTypelist box on the right, select14-Mar-2012and click OK.

The date format we selected is now applied to the numbers in column B.

Use:we can useCustomcategory options in the Format Cells dialog if you want to apply custom date formats.

Method #4 – Use the special paste dialog box to convert text to date in Excel

Hespecial pasteThe dialog allows us to paste data in various ways such as values, formulas, formats, etc. We can use the dialog box to convert text to date in Excel.

The following sample dataset contains text strings with date formats accepted by Excel.

We use a dataset to demonstrate how to use special paste to convert text to regular dates.

We use the following steps:

1. Type the number 1 in any blank cell on the worksheet. In this case, we enter the value 1 in cell B1.
2. Select cell B1 which contains the number 1 and pressControl + Cto copy it.

Notice the running ants outline in cell B1, indicating that the value 1 has been copied to the Excel clipboard and is available for pasting.

3. Select the cell range A2:A6 that contains the text strings.

4. TypeCtrl+Alt+Vto activate the Paste Special dialog box (or right-click and click Paste Special).

We can also activate the Paste Special dialog by clicking the buttondomopen bookmarks filePastadeveloped inSavegroups and selectionspecial pasteoption as below:

5. wModeselection dialog sectionMultiplyoption and click OK.

Text strings in column A are immediately converted to numbers representing dates.

Use:What we did with the Paste Special function is equivalent to using a formula to multiply text strings by the number 1, as explained in method #3. We can also use the Add option in the dialog box.

We apply the date format to the numbers by doing the following:

6. Select the cell range A2:A6.
7. Ofdompointer insideNumbergroup, opennumerical formdrop-down menu and select one of thema short datelubricanta long date. In this case, we choose Long Date.

Long date format is applied to numbers.

If we want to have better control over date formatting, we can use the Format Cells dialog box as explained in Method #1.

Convert text to dates in Excel (for text date format not compatible with Excel)

This section of the tutorial is about converting text strings with date formats not supported by Excel to regular dates in Excel.

Sometimes, when we import data containing dates into Excel from the Internet or other external databases, the date information may have date formats that Excel does not support.

For example, the following dataset contains date information that uses periods to separate the day, month, and year.

The default date separators that Excel accepts are hyphen (-) and forward slash (/).

Therefore, Excel does not recognize this date data as dates, but as text strings because they are dot delimited.

If we use the DATEVALUE or VALUE function as we did in method #1 and method #2 in the previous section to convert text strings to numbers representing dates, we get#PROWESS!error.

DATEVALUE or VALUE returns #VALUE! error because it does not recognize date strings with period delimiters as valid arguments.

To change the format of the text values ​​in column A to the date formats accepted by Excel, we need to replace the period separator with a hyphen (-) or slash (/).

We can use the following three techniques to replace the separator period with a separator acceptable to Excel.

Method #5 – Use Find and Replace to change the separator to a slash or dash

Hefind and replaceA function in Excel allows us to search for specific text or values ​​in a data set and replace them with new values.

So we can apply this function to find the points in our data set and replace them with separators accepted by Excel.

In this case, we'll replace the points in the following sample data set with a hyphen (-):

Use:If your data set has a separator other than a period, replace the period separator used in this example with a delimiter in your data set.

We apply the following steps:

1. Select the cell range A2:A6 that contains the text values.

2. Ofdomtab insideversiongroup clickfind and selectbutton and selectsubstituteselection from the drop-down list.

We can also press a shortcutCtrl + HOpenfind and replaceDialog window.

3. Type a period (.) infind thisbox and hyphen (-) insideexchangefield and clickreplace allbutton.

Points in the data set are immediately replaced with dashes.

Notice that the values ​​are now right-aligned, which means Excel recognizes them as normal dates.

4. Click OK on the information pop-up indicating the number of transactions made.

5. Click Close on the Find and Replace dialog box.

Read also:How to remove hyphens (-) in Excel?

Method #6 – Use text in columns to convert text to date in Excel

Stand outtext in columnsThe function allows us to split the text in a single cell into separate cells.

Also, this function can consolidate split data into one date.

Suppose we have the following date data set in text format with periods separating the month, day, and year.

We want to use the Text to Columns function to convert text strings to regular dates in Excel.

We use the following steps:

1. Select the cell range A2:A6 that contains the text values.

2. OfDanisheyelash wdata toolsgroup clicktext in columnsbutton.

3. ChoosePerimeterofGuide to converting text to columns - Step 1 of 3dialog box and clickNext.

4. Make sure none of the options are selectedlimitersoption group toGuide to converting text to columns - Step 2 of 3dialog box and clickNext.

5. OfGuide to Converting Text to Columns - Step 3 of 3dialog box, selectData, Come in=$B$2wDESTINYfield and clickFlap.

The text strings in column A have been converted to normal dates in column B.

Note that some dates are separated by dashes and others by slashes. We can apply a single date format to dates using the Format Cells dialog as explained in the following steps:

6. Select the cell range B2:B6 that contains regular dates.

7. TypeControl + 1to open the Format Cells dialog.
8. OfNumberbookmark optionDataofCategorylist box, select the format you wantTypelist box on the right and click OK. In this case we chooseMarch 14, 2012 r.

The single date format is suddenly applied to the regular dates in column B.

Method #7 – Use the SUBSTITUTE and DATEVALUE functions to convert text to numbers representing dates

HeREPLACEthe function replaces the existing text with a new text value in a text string and the functionFECHAVALOthe function changes the date in text format to a number that represents the date.

We can use a formula that combines these two functions to convert dates in text format to numbers that represent dates.

We can then apply the date format to the numbers to display the dates in a human-readable format.

Consider the following data set that contains dates in text format with a period separator separating months, days, and years.

We want to use a formula that combines the SUBSTITUTE and DATEVALUE functions to convert the text strings in column A to numbers that represent the dates in column B.

We apply the following steps:

1. Select cell B2 and enter the following formula:

=VALORDEFECHA(SUSTITUIR(A2, ".","-"))

Use:We can use the VALUE function instead of the DATEVALUE function and get the same result.

2. Click Enter on the formula bar.
3. Double-click or drag the fill handle to copy the formula down the column.

The numbers representing the dates appear in column B.

4. Select the cell range B2:B6 that contains the numbers that represent the dates.

5. Ofdomeyelash wNumbergroup, opennumerical formdrop-down menu and select one of thema short datelubricanta long date. In this case, we choose Long Date.

Text strings in column A are now converted to regular dates in column B.

Method #8: Convert the date from year XX to 20XX (2-digit year to 4-digit year)

Sometimes we get a dataset with date information in text format with a two-digit year instead of a four-digit year.

The following sample dataset contains dates in text format with two-digit years.

we can useReplace XX with 20XXchoice insmart tagdropdown to convert text strings to valid dates in Excel.

We use the following steps:

1. Select the cell range A2:A6 that contains the text values.

Notice the yellow smart tag next to cell A2. We can hover over a smart tag to find out why it appears in the dataset.

In this case, the smart tag appears on the dataset because the dataset contains date strings represented by only two digits representing the year.

2. Click the down arrow on the smart tag to open the drop-down list and selectReplace XX with 20XXselection.

Use:we can useReplace XX with 19XXoption if the years were in the 20s^czage.

Text strings are immediately converted to regular dates.

Use:We should only use this method when all dates are in 19XX format or all dates are in 20XX format.

Method #9: Use text functions to convert text to date in Excel

Excel has many text functions that allow us to manipulate and extract information from text strings. We can use formulas that combine text functions to convert text to date in Excel.

We give examples of using formulas that combine text functions to convert text to a date in Excel.

Example #1: Converting an 8-digit text date in the DDMMYYY date format to a regular date

Sometimes we may receive a dataset of 8-digit text dates without a delimiter separating the month, day, or year, as in the example below.

Notice the regular pattern in the text values: the first two digits represent the day, the next two digits represent the month, and the last four digits represent the year.

We want to use a formula that combines the DATE, RIGHT, MIDDLE, and LEFT functions to convert the text strings in column A to regular dates in column B.

We proceed as follows:

1. Select cell B2 and enter the following formula:

=DATOS(PRAWA(A2;4);ŚRODEK(A2;3;2);LEWA(A2;2))

2. Click Enter on the formula bar.
3. Double-click or drag the fill handle to copy the formula down the column.

The formula returns the correct dates in column B.

RIGHT outputs the last four digits representing the year, MID outputs the two month numbers following the first two digits of the day, and LEFT outputs the first two digits representing the day.

Finally, the DATE function used the values ​​returned by the string functions as arguments to create a valid date.

Example 2: Convert suffixed text dates to regular dates

Sometimes we may need to work with datasets that have text dates with suffixes attached to day values, as in the following sample dataset:

We can use a formula that combines the DATEVALUE and SUBSTITUTE functions to remove the suffixes and convert text dates to numbers that represent valid dates.

We apply the following steps:

1. Select cell B2 and enter the following formula:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"th",""),"st",""),"rd",""),"nd",""))

2. Click Enter on the formula bar.
3. Double-click or drag the fill handle to copy the formula down the column.

The text strings in column A are converted to numbers representing the dates in column B.

Now we'll apply the date format to the numbers in column B by doing the following:

4. Select the cell range B2:B6 that contains the numbers that represent the dates.

We use the REPLACE function to replace suffixes with empty strings.

We use four SUBSTITUTE functions because there are four possible suffixes to add to the day value: st, nd, rd, and th.

After replacing the suffixes with empty strings, the DATEVALUE function converts text strings to numbers representing dates.

5. Ofdomeyelash wNumbergroup, opennumerical formdrop-down menu and select one of thema short datelubricanta long date. In this case, we choose Long Date.

The text strings in column A have been converted to valid dates in column B.

We can use the Format Cells dialog as explained in Method #1 to apply custom date formats or other built-in date formats that are not available in the Number Format drop-down list.

Example 3: Convert text dates with day names to regular dates in Excel.

Sometimes we can get datasets with text dates that have day names, as in the following example:

We can use a formula that combines the DATEVALUE, RIGHTS, LENGTH, and FIND functions to remove the day names from the dataset and convert the remaining text strings to numbers that represent dates.

We apply the following steps:

1. Select cell B2 and enter the following formula:

=DATEVALUE(ENtitlement(A2,DEBT(A2)-FIND(" ",A2)))

2. Click Enter on the formula bar.
3. Drag or double-click the fill to copy the formula to the column.

The FIND function returns the position number of the first space in a text string.

The position number is then subtracted from the length of the text string and the result is fed into the RIGHT function.

The RIGHT function then uses the result as an argument to extract the text date without the day name.

Finally, the text string returned by the RIGHT function is fed to the DATEVALUE function, which returns a sequence number representing the date.

4. Select the cell range B2:B6 that contains the numbers that represent the dates.
5. Ofdomeyelash wNumbergroup, opennumerical formdrop-down menu and select one of thema short datelubricanta long date. In this case, we choose Long Date.

Text dates in column A with day names have been replaced with regular dates in column B.

This tutorial shows you different techniques to convert text to date in Excel. Assume that the text strings have date formats supported by Excel.

In this case, we can use the DATEVALUE and VALUE functions or the Paste Special function to create serial numbers that represent dates.

We can then apply date formats to the serial numbers to display the dates in a human-readable format.

We can also use numeric formulas to convert text strings to serial numbers that represent dates, and then apply date formats to the numbers.

If the text strings have date formats that Excel doesn't support, we can use formulas that combine text functions to manipulate the text strings and convert them to normal dates.

We hope you found the tutorial useful.

Other Excel articles you might like:

• How to insert date in Excel? (Shortcut + Types)
• How to convert date to serial number in Excel?
• How to remove year from date in Excel?
• How to separate date and time in Excel?
• How to combine date and time in Excel?
• How to highlight dates before today in Excel?
• How to calculate the number of months between two dates in Excel?