Excel is a powerful tool for managing and analyzing data, but working with dates in Excel can be frustrating when they are in text format. Fortunately, you can convert your text data into a proper date format, making it easier to sort, filter, and perform calculations on your data.

In this guide, we will show you how to convert text to date in Excel, so you can get the most out of your data.

Text Date vs. Regular Date in Excel

Excel stores date as numerical values, enabling you to use them in calculations just like regular numbers. This also allows you to format dates or even sort your data by date, which you can't do when dates are in text format.

For example, the date format 14-Feb-2023 is valid in Excel. If you type it in a cell, it would show as a date, but Excel would store it as 44971, which is the number that corresponds to the date 14-Feb-2023.

However, if you write 14.Feb.2023, it'll be treated as a text string because it is not a valid date format. Therefore, you can't use it in calculations because it isn't stored as a number.

How to Convert Text to Date in Excel

If you have text in your Excel sheet you want in date format, the following are various methods you can use to convert the text to date in Excel.

1. Using the DATEVALUE Function

The DATEVALUE function converts text strings into serial numbers, which you can change to acceptable date formats. Here's how to convert your text to date using the DATEVALUE function.

  1. Open your Excel document and find the text string you want to change.
  2. Select a blank cell next to the cell containing the text.
  3. Enter =DATEVALUE into the blank cell or formula box, followed by the cell number containing the text in parentheses. For example, if the text string is in C26, enter =DATEVALUE(C26).
    Applying DATEVALUE function to Selected Column in Excel

Now that you've converted the text into numbers, you need to change the numbers to date. Here's how.

  1. Select the cells with the DATEVALUE numbers.
  2. Click on the Home tab and go to the Number section.
  3. Click on the down arrow next to the toolbox that says General and select Short date or Long date from the menu.
    Options to Convert DATEVALUE Results to Dates

You can use custom formatting in Excel if you don't want to use the default date settings. Follow the steps below.

  1. Highlight the cell(s) you want to edit and hit Ctrl + 1 on your keyboard.
  2. When the Format Cells window appears, click on the Number tab at the top.
  3. Under Category, choose Date.
  4. Choose the date type you prefer from the list on the right.
    Options to Customize Date Format in Excel
  5. Click OK to save your changes.

2. Using the VALUE Function

If your text represents a date, you can use the VALUE function in Excel to convert it into numerical values, which you can change to dates. Here's how to convert text to date using the VALUE function.

  1. Open your Excel file and find the text you want to convert to numbers.
  2. Select an empty cell next to the one with the text.
  3. Enter =VALUE into the empty cell.
  4. Type the number of the cell containing the text in parentheses. For example, if the text is in B15, enter =VALUE(B15) in the empty cell.
    Applying VALUE Function to Selected Column in Excel
  5. Hit Enter.

You'll get a serial number, which you can then convert to an actual date.

3. Using Text to Columns

If the text contains numbers that are separated by spaces, periods, or slashes, you can use Text to Columns to convert it to date without using a formula. Here's how.

  1. Open the Excel file you want to edit.
  2. Choose the column of text you want to convert to date.
  3. Click on the Data tab at the top of the screen.
  4. Click on Text to Columns under the Data Tools section.
    The Text to Columns Option in Excel
  5. When the Text wizard appears, choose the Delimited option and hit Next.
  6. In the next window, uncheck all the boxes under Delimiters, including Tab, Semicolon, Comma, Space, and Other.
    Unchecking all boxes under the Delimiters Option
  7. Click Next when you're done.
  8. In the next window, select Date under the Column data format section.
  9. Click on the drop-down menu next to Date and choose your preferred date format.
    Selecting a Preferred Date Format
  10. Hit Finish to save the changes and close the text wizard.

Change the Date Format in Excel for Easy Data Analysis

Converting text to date is useful for effective data analysis and manipulation. By using various Excel functions and formatting options, you can easily convert text values into date values, which you can then use in calculations, charts, and other data analysis tools.