Excel Convert Text To Date Example

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.

1. When you want to check whether a cell date is a text format or date format, you can use the below methods.
2. If a cell data use the excel date format, then the cell date text is right-aligned.
3. If a cell data use the excel text format, then the cell date text is left-aligned by default.
4. 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.
5. 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.
6. 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 ).
7. 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.

1. If the text’s format in an excel cell matches the regular excel date format, then you can convert it to excel date manually.
2. For example, select a cell and right-click it, then click the Format Cells… menu item in the popup menu list.
3. Click the Number tab, and select the item Text in the Category: list, click the OK button to save and exit the dialog.
4. 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.
5. Click to select the cell again, and press Ctrl + 1 to open the Format Cells dialog window again.
6. 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.
7. 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.
8. Press Ctrl + 1 to open the Format Cells dialog again, and click the Number tab.
9. When you select the item Text from the Category: list again, you will see a serial number on the dialog right side.
10. 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.
11. 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.
12. 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.

1. Besides the above method, you can also use an excel function to convert regular date format text to date values in excel.
2. The function is DATEVALUE(date_text), the parameter must be a regular excel date format text.
3. If it does not match any excel date format type, then the function return #VALUE!.
4. 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.
5. 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.
6. 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.

1. 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.
2. 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).

1. The regular excel date format type text uses the forward slash to separate year, month, and day.
2. But if the date text uses a backslash as the delimiter in the text, the DATEVALUE function can not convert it.
3. 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.
4. First, we input the text 2022\9\9 in cell A1.
5. Then we can use the function LEFT to get the year text such as LEFT(A1,4).
6. Use the function MID to get the month text such as MID(A1,6,1).
7. Use the function RIGHT to get the day text such as RIGHT(A1,1).
8. 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.
9. We pass the parsed out year, month, and day value to the DATE function to create an excel date value.
10. You can see that cell B1‘s format type is Date.

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

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.
2. Input the text 2022 June 1 in cell A1.
3. 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).

1. In this case, the date text use dot as the delimiter such as 2022.6.1.
2. 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).

1. If the date text contains a month name with a comma after it such as 1 June, 2022.
2. 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).

1. If the date text contains the weekday name such as Tuesday, Jan 1, 2021.
2. 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).

1. In this case, the weekday name is located at the end of the date text such as 2022-9-19, Jan.
2. 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).

1. If the date text contains a suffix in the day such as 16th Jun 2021.
2. 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).

1. If the date text uses space as the delimiter such as 16 6 2022.
2. 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).

1. If the date text contains a serial number without any delimiters such as 2022616.
2. 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.

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

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

1. 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.
2. You can use the VALUE function to get the excel date value integer number.
3. 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.
4. If you change cell B1‘s data type to Short Date from the Home tab, Number group, Number Format drop-down list.
5. 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.

1. 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.