Excel Formula: Convert Text to Excel Date
In this tutorial, it provides multiple formulas to convert a text string to Excel standard date.
If you want to follow along with this tutorial, please download the example spreadsheet.
DATEVALUE function
Generic formula:
| DATEVALUE(text_date) |
Syntaxt and Arguments
| Text_date: the text string that you want to convert to the standard Excel date. |
Return Value
The formula returns a series number.
Remark
This formula only can be used to convert text dates to Excel dates, if there is time data existed, the formula will ignore the time data.
How this formula work
In cell C3 to place below formula to convert the text strings in cell B3:B5 to Excel dates.
| =DATEVALUE(B3) |
Press Enter key and drag fill handle down to cell C5. All text strings have been converted to related series numbers.
Then format the series numbers to date format. Keep the series numbers selected, press Ctrl + 1 to enable the Format Cells dialog, then under Number tab, choose Date from the Category list, then select one date type from the right Type section.
Click OK, the texts have been converted to Excel datetime.
Explanation
DATEVALUE function: Convert a date stored as text to a valid date.
VALUE function
Generic formula:
| VALUE(text_date) |
Syntaxt and Arguments
| Text_date: the text string that you want to convert to the standard Excel date. |
Return Value
The formula returns a series number.
How this formula work
In cell C3, place below formula to convert the text strings in cell B3:B5 to Excel dates.
| =VALUE(B3) |
Press Enter key and drag fill handle down to cell C5. All text strings have been converted to related series numbers.
Then keep the results selected, then press Ctrl + 1 to display the Format Cells dialog, under Number tab, select Date from the Category list, then in the right section of Type, choose one format you need.
Click OK then the formula results have been formatted as datetime.
Explanation
VALUE function: Convert text to number.
Mathematical Operation
You can use the mathematical operations to convert the text date to a valid date in Excel. However, remember, do not change the actual value.
Generic formula:
| Text_date+0 |
| Text_date*1 |
| Text_date/1 |
| --Text_date |
How this formula work
Please use formula as below:
Then format the results to the datetime format as you need.
Relative Formulas
- Convert Unix Timestamp to Excel Time or Vice Inverse
This tutorial provides formulas to convert between Unix time and Excel time. - Convert Decimal Seconds To Time
To convert decimal seconds to Excel time in Excel, a formula can help you. - Convert Time to Decimal Hours
Here provides a formula that converts time to decimal hours in Excel and explains how the formula works. - Convert Time To Decimal Minutes
In this tutorial, it provides a formula for converting time to decimal minutes in Excel.
Relative Functions
- Excel IF Function
Test for specified conditions, then returns the corresponding values - Excel VALUE Function
Convert text to number. - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date. - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.