Microsoft Excel is a powerful spreadsheet application that professionals regularly use to collect and analyze data. In Excel, one can easily organize and analyze massive datasets by date.
Sometimes, things get complicated when those dates are downloaded from online sources or imported from other spreadsheet programs.
You may see a five-digit number in the place of a date, which is confusing.
Knowing how to convert these numbers or text to dates is critical for further data analysis.
Welcome to this blog, where we will examine the steps to convert text string to date and vice versa. Make sure to read this article until the end, as it contains a lot of helpful information for organizing and analyzing Excel spreadsheets by date.
Difference Between Text Date and Excel Date
Understanding the difference between text dates and Excel dates is very important.
Some essential differences will help you differentiate between the text date and the Excel date.
| Text Date | Excel Date | |
|---|---|---|
| Storage Format | Stored as a string of characters | Stored as a numeric serial number |
| Functionality | It can be used directly in date operations | It can be used directly in date operations |
| Formatting | Requires text manipulation or conversion | Easily changeable using built-in date functions |
| Sorting | May sort incorrectly as text | Sorts correctly as a date |
| Calculations | Needs conversion before performing calculations | Can be used directly in calculations |
| Example Representation | January 1, 2024 2024-01-011st January, 2024 | Serial number like 4492701-Jan-2024 |
Download the Example Excel Sheet
Before we proceed further in this article, consider downloading the following Excel that contains the datasets we used to demonstrate the process of converting text to data.
It will strengthen your knowledge of the formulas through practice. Skip downloading the above Excel if you have your own ready to practice the things we are about to discuss.
How to Convert Text String to Date in Excel
After exporting the datasets from an online source, you may notice that the dates are in text format.
Let’s consider the following example. Here, the hiring dates are represented as text strings.
Task: Convert the hiring dates, which are in text format, to valid dates that Excel understands. As shown above, we have 20 records to convert.
To simplify the learning, we will divide the entire process of date conversion into two steps.
STEP #1 – Converting the Text String to a Serial Number
This is the first step, in which we convert the given dates to respective serial numbers using the DATEVALUE function.
Here are the steps,
- Open your desired Excel spreadsheet
- Select the target cell and type “=DATEVALUE”
- Select the first option from the popup or press “Tab” on your keyboard
- Provide the date string enclosed in double quotations as “28-Sep-92” or specify the cell reference as “B2”
- End the formula with a closing parenthesis “)”
- Press “Enter” on your keyboard
The final formula should look like below,
=DATEVALUE(B2)This formula would convert all the dates in text format to serial numbers within a few seconds, as shown in the above GIF.
To apply this formula to the rest of the entire column, hover to the lower-rightmost corner of the cell where we just used the DATEVALUE function. You will see a “+” icon. Click on that “+” icon and drag the formula to the end of the table.
Explained: The DATEVALUE Function
It helps users convert a date into a serial number recognizable by Excel and other spreadsheet programs.
The general syntax of this formula is as follows,
=DATEVALUE(date_string)Here, the “date_string” is the date in text format or the cell reference containing the date string you want to convert.
Things to take care of while using the DATEVALUE function
Some precautions must be taken while using the DATEVALUE function. If you see an error, it will be due to one of the following reasons.
- Incorrect Date Format – Ensure that the date formats are acceptable by Excel. You can identify the date format that is compatible with Excel by looking at it. The correct date format will be aligned to the right of the cell after you press “Enter,” and the remaining ones will stay on the left only. Refer to the following image.
- Space – Check for any leading or trailing spaces within the text string. If there are any spaces, they can be removed by combining the TRIM function with the DATEVALUE function.
- Consistent Date Formatting – Ensure consistency in date formats within your dataset. Mixing various formats may lead to errors like the “#VALUE!” error.
STEP #2 – Converting the Serial Number to Date
The DATEVALUE function in Excel returns a serial number instead of conventional dates. You need to convert those serial numbers to dates using one of the following methods.
METHOD #1 – By changing the Cell Formatting
Here are the steps,
- Open the desired spreadsheet
- Select the entire column containing serial numbers that you want to format as dates
- Go to the “Home” tab of the main menu
- Find the “Number” section and click on the dropdown as shown below,
- You can choose from one of the predefined date formats in the list. Or, click on the “More Number Formats” for custom formatting
- Next, in the “Format Cells” dialogue box, click on the “Number” tab at the top and choose the “Date” category as shown below,
- Now, choose from the variety of custom date formats
- Once you finish, click on “OK” to apply the changes
It will instantly change the serial numbers to dates, as shown in the above GIF.
METHOD #2 – By using the TEXT function in Excel
Using the TEXT function is one of the quickest methods to convert serial numbers to valid dates in Microsoft Excel.
Here are the steps,
- Add a new column to the table
- Click on the first cell of the newly added column
- Type “=TEXT”
- Select the first option from the popup or press “Tab” on your keyboard
- Replace the “value” argument with the cell reference “C2”, holding the serial number
- Press “,” on your keyboard to move to the next argument
- For the format_text argument, we will use the “dd/mm/yyyy” date format
(Make sure to enter the format within double quotation marks “ ”) - End the formula with a closing parenthesis “)”
- Press the “Enter” button to see the result
The final formula should look like below,
=TEXT(C2,”dd/mm/yyyy”)You can use the Fill Handle, as discussed above, to apply the formula to the rest of the column’s cells.
Explained: The TEXT Function
The TEXT function is used to convert a numeric value to text. It is specifically used to format the dates and times.
Here is how the general syntax for the function looks,
=TEXT(value, format_text)Each argument of the above formula must be replaced with proper information.
- “value” – This is the numeric value that you wish to convert to text. You can enter the value manually or provide the cell reference
- “format_text” – This argument lets you specify the desired output format. Make sure to use the double quotation marks
Alternative to Convert Text to Date in Excel
The above method is lengthy because it involves using the DATEVALUE function and changing the format of the cells.
So, here’s a built-in feature that gets the job done quickly.
Consider the following table with employee details, including their name and hiring date.
Our task is to convert all the values from the second column to the valid date.
Let’s begin,
- Select the entire second column holding dates as a text string
- Hover to the main menu and click on “Data” tab
- Select the “Text to Columns” tool as shown below
- A new popup will appear on your screen, as shown below,
- Make sure the radio button before the “Delimited” option is ticked
- Press the “Next” button
- Uncheck all the delimiter boxes and click on “Next” as shown below
- In the last step, select “Date” under “Column data format”, as shown in the following image,
- You can also choose from the different date formats available in the dropdown
- Once done, click on the “Finish” button to see the final result
It is a quick alternative to converting the text string to a date in Excel. If all the given dates follow an identical text string format, you can use it.
How to Convert an 8-digit Number to Date in Excel
Sometimes, there are situations where the dates are represented by eight-digit numbers, as shown in the following image,
To convert those 8-digit numbers to a valid date, you need to extract the day, month, and year. For this purpose, we will use the LEFT, MID, and RIGHT functions in Excel.
Before we jump into the actual steps, let us first understand the general syntax of each function.
Explained: The LEFT function
It retrieves several characters, counting from the left side of a cell. Here’s the general syntax for the function,
=LEFT(text, [num_chars])Where,
- “text” – It is the argument representing the text string from which you want to extract the substring
- “num_chars” – This is an optional argument used to specify the number of characters to extract
Explained: The MID function
It is designed to pull a substring from the middle of the original text string in Excel.
The syntax of the MID function is as follows,
=MID(text, start_num, num_chars)Where,
- “text” – It is the original text string from which you wish to extract the test string
- “start_num” – Here, you can define the position of the first character
- “num_chars” – Here, you can define the number of characters to extract
Note that all three arguments need to be defined to avoid the errors.
Explained: The RIGHT function
It returns the specified number of characters from the end of a text string. The general syntax for the RIGHT function is as follows,
=RIGHT(text, [num_chars])Where,
- “text” – It represents the original text string from which you want to extract a substring
- “num_chars” – Here, you can define the number of characters to extract
Explained: The DATE function
The DATE function in Excel creates a date value based on the given year, month, and day.
In other words, the function is mainly used when you need to construct a date when year, month, and day are available across separate cells.
The syntax of the DATE function is as follows,
=DATE(year,month,day)The function’s three arguments are self-explanatory. Make sure you replace them with the proper information.
Consider the following table, which has 8-digit numbers in the first column. Our task is to convert them to a valid date.
Before that, carefully observe the first 8-digit numbers; you will see that the first two digits represent the month, followed by them, the next two digits represent the day, and the last four digits represent the year.
To get the date, let’s combine the DATE, RIGHT, MID, and LEFT functions.
STEP #1 – Get the year from an 8-digit number
As the year is at the end of the text string, we need to use the RIGHT function. As the first 8-digit number is available in the cell “A1”, our formula will be as follows,
=RIGHT(A1,4)It will extract the last four characters of the text string cell A1.
STEP #2 – Get the month from the 8-digit number
The first two digits represent the month, so we will use the LEFT function to extract the month.
Our formula will be as follows,
=LEFT(A1,2)STEP #3 – Get the day from the 8-digit number
The day is in the middle, so we need to use the MID function.
Let’s begin,
As the day is available at the 3rd and 4th position, we will replace the “starting_at” argument with the number “3” and “extract_length” with the number “2”. Our final formula will be as follows,
=MID(A1,3,2)STEP #4 – Get the Date
We will combine all of the formulas we have used between steps 1 to 3 and create a formula as shown below,
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))
How to Convert a Date to Text String in Excel
In the previous section of this article, we learned the steps to convert a text string to date.
But what if we need to convert a date value to a text string?
Let’s learn that,
- Select the cell(s) containing the date values
- Click on the “Home” tab of the main menu
- Next, go to the number formatting section and click on the dropdown as shown below,
- Select the “More Number Formats” option from the list
- A new dialogue box named “Format Cells” will appear on the screen
- Locate the “Number” tab at the top, and click on the “Text” option as shown in the following image,
- Click the “OK” button to close the popup
All your dates are now converted to their respective text strings.
You can check the format of the cells by right-clicking on them and choosing the “Format Cells” option from the list.
Conclusion
Converting text strings to dates and vice-versa in Excel is essential for accurate data analysis and calculations based on dates.
It also ensures proper formatting and consistent data entry to avoid errors.
I hope this article has taught you all the details about converting a text string to a date. Please comment below if you need help or encounter errors while converting text strings to dates. I will do my best to answer your questions as soon as possible.
For more such tips and tricks to use Excel like a pro, make sure to explore our blog section.