When you import text data from a .csv file or a database into excel, you may find the imported date column value is saved as text format. But you need to change the text value to excel date format to process them using the excel functions. This article will tell you how to convert text to date in excel with examples.

### 1. How To Check Whether A Cell Data Is Text Format Or Date Format.

- When you want to check whether a cell date is a text format or date format, you can use the below methods.
- If a cell data use the excel date format, then the cell date text is right-aligned.
- If a cell data use the excel text format, then the cell date text is left-aligned by default.
- When you select the date format cell and click the
**Home**tab on the excel top ribbon area, the**Date**item is selected by default in the**Number**group**Number Format**drop-down list. - But if the cell saves a text format date value, the
**Home**tab**Number**group**Number Format**drop-down list will select the**General**item by default. - When you select multiple date format cell, you can see text like
**Average:, Count:, Sum:**in the excel status bar( located at the excel window bottom right corner ). - If the cell format is text, when you select multiple cells, the status bar will show the text like
**Count:**only.

### 2. Convert Regular Date Format Text To Date In Excel Without Function.

- If the text’s format in an excel cell matches the regular excel date format, then you can convert it to excel date manually.
- For example, select a cell and right-click it, then click the
**Format Cells…**menu item in the popup menu list. - Click the
**Number**tab, and select the item**Text**in the**Category:**list, click the**OK**button to save and exit the dialog. - Input text
**2022-01-01**in the cell ( this text format matches a regular excel date format ) and you can find it is aligned to the cell’s left side, which means it is a text format. - Click to select the cell again, and press
**Ctrl + 1**to open the**Format Cells**dialog window again. - Click the
**Number**tab, then select**Date**from the**Category:**list, select**2010-03-14**from the**Type**list on the right, and click the**OK**button to save and exit. - You will find the date text has aligned to the right side of the cell, this means excel has converted the text to an excel date format value.
- Press
**Ctrl + 1**to open the**Format Cells**dialog again, and click the**Number**tab. - When you select the item
**Text**from the**Category:**list again, you will see a serial number on the dialog right side. **This is because excel saves each date or time cell value as an integer number. And number 1 is used to save the date 1900-01-01.**- So if you know a date value’s integer number, if you want to convert it to an excel date text, you can input it in the excel cell, and select
**Date**from the excel**Home**tab**Number Format**drop-down list in the**Number**group. **If you want to get all the regular excel date format types**, you can press**Ctrl + 1**to open the**Format Cells**dialog window, then click the**Number**tab, click**Date**in the**Category:**list, then you can see the regular date string format type on the right side**Type**list.

### 3. Convert Regular Date Format Text To Date In Excel With DATEVALUE Function.

- Besides the above method, you can also use an excel function to convert regular date format text to date values in excel.
- The function is
**DATEVALUE(date_text)**, the parameter must be a regular excel date format text. - If it does not match any excel date format type, then the function return
**#VALUE!**. - For example, we input text
**2022-09-09**in cell**A1**and set the cell format type to**Text**from excel**Home**tab**Number**group**Number Format**drop-down list. - Then we can input the formula
**=DATEVALUE(A1)**in cell**B1**, when you press the**Enter**key, it will covert the date text in cell**A1**to the excel date value and display the text version in**B1**. - If you change the
**A1**cell’s text to**2022\9\9**which is not a regular date format type text in excel, then you will get the value**#VALUE!**in cell**B1**, because**2022\9\9**is not a regular date format type text in excel.

### 4. Convert Irregular Date Format Text To Date In Excel.

- If the cell text is irregular date format text, you need to use some excel functions to parse out the related year, month, and day values and then use the excel
**DATE**or**DATEVALUE**function to convert it to excel date type value. - Below are some examples of how to convert irregular excel date format text to date value.

#### 4.1 Use Back Slash As Date Text Delimiter (2022\9\9).

- The regular excel date format type text uses the forward slash to separate year, month, and day.
- But if the date text uses a backslash as the delimiter in the text, the
**DATEVALUE**function can not convert it. - For example,
**2022\9\9**is such a date format text, it is irregular for excel date format, we can use the below function to parse out the year, month, and day. - First, we input the text
**2022\9\9**in cell**A1**. - Then we can use the function
**LEFT**to get the year text such as**LEFT(A1,4)**. - Use the function
**MID**to get the month text such as**MID(A1,6,1)**. - Use the function
**RIGHT**to get the day text such as**RIGHT(A1,1)**. - Now we can input the formula
**=DATE(LEFT(A1,4),MID(A1,6,1),RIGHT(A1,1))**in cell**B1**to get the date value. - We pass the parsed out year, month, and day value to the
**DATE**function to create an excel date value. - You can see that cell
**B1**‘s format type is**Date**.

#### 4.2 Use Month Name In Date Text(2022 June 1).

- If the date text contains the month name such as 2022 June 1, then we can use the below formula to convert it into an excel date value.
- Input the text
**2022 June 1**in cell**A1**. - Then input the formula
**=DATEVALUE(RIGHT(A1,1)&”-“&TEXT(MID(A1,6,3),”MMM”)&”-“&LEFT(A1,4))**in cell**B1**and press enter key, then you will get the text date value in cell**B1**.

#### 4.3 Use Dot As The Date Text Delimiter(2022.6.1).

- In this case, the date text use dot as the delimiter such as
**2022.6.1**. - We can use the formula
**=DATE(LEFT(A1,4),MID(A1,6,1),RIGHT(A1,1))**to convert the date text to excel date value.

#### 4.4 The Date Text Contains Month Name With A Comma After It(1 June, 2022).

- If the date text contains a month name with a comma after it such as
**1 June, 2022**. - We can use the formula
**=DATEVALUE(LEFT(A1,1)&”-“&TEXT(MID(A1,3,3),”MMM”)&”-“&RIGHT(A1,4))**to convert it to excel date value.

#### 4.5 Add Day Name In The Date Text(Tuesday, Jan 1, 2021).

- If the date text contains the weekday name such as
**Tuesday, Jan 1, 2021**. - We can use the formula
**=DATEVALUE(MID(A1,FIND(“,”,A1)+6,1)&”-“&MID(A1,FIND(“,”,A1)+2,3)&”-“&RIGHT(A1,4))**to convert it to excel date value.

#### 4.6 The Day Name Is Located At The End Of The Date Text(2022-9-19, Jan).

- In this case, the weekday name is located at the end of the date text such as
**2022-9-19, Jan**. - We can use the formula
**=DATEVALUE(MID(A1,6,1)&”-“&MID(A1,8,2)&”-“&LEFT(A1,4))**to convert the text to date in excel.

#### 4.7 Day With Suffix In The Date Text(16th Jun 2021).

- If the date text contains a suffix in the day such as
**16th Jun 2021**. - Then you can use the formula
**=DATEVALUE(LEFT(A1,FIND(“th”,A1)-1)&”-“&MID(A1,FIND(” “,A1)+1,3)&”-“&RIGHT(A1,4))**to convert it to date in excel.

#### 4.8 Date Text Use Space As Delimiter(16 6 2022).

- If the date text uses space as the delimiter such as
**16 6 2022**. - We can use the formula
**=DATE(RIGHT(A1,4),MID(A1,4,1),LEFT(A1,2))**to convert the text to date in excel.

#### 4.9 Date Text Contains Integer Number Only Without Space(2022616).

- If the date text contains a serial number without any delimiters such as
**2022616**. - We can use the formula
**=DATE(LEFT(A36,4),MID(A36,5,1),RIGHT(A36,2))**to convert the text to date in excel.

#### 4.10 Quick Method To Convert Text To Date In Excel.

- If you do not want to use the excel functions to convert text to excel date value, you can use the below quick method.
- You can use the formula
**=A1+0**,**=A1*1**,**=A1/1**,**=–A1**in an excel cell to convert the text to excel date value. - If the cell’s
**Number Format**is**General**, then it will only show the date integer value in the cell. - You should select the item
**Short Date**from the**Home**tab,**Number**group, and**Number Format**drop-down list for the cell. - Then you can see the date text version in the excel cell.

### 5. How To Get Excel Date Integer Number Value From Date Text.

- Because excel saves the date value as an integer number inside, now I will tell you how to get the integer number value from a date text in excel.
- You can use the
**VALUE**function to get the excel date value integer number. - If cell
**A1**contains the text**2020-9-9**, then you can input the formula**=VALUE(A1)**in another cell such as**B1**to get the date integer value in cell**B1**. - If you change cell
**B1**‘s data type to**Short Date**from the**Home**tab,**Number**group,**Number Format**drop-down list. - Then you can see the integer number’s date string in the cell.

### 6. Use Text To Columns Wizard To Convert Multiple Rows Of Text To Date In Excel.

- Please read the article How To Use Excel Text To Columns Wizard To Convert Simple (Consistent) & Complex (Inconsistent) Date Format Text To Date to learn more.