How to Convert to Date in Excel (3 Common Cases)

In this Excel tutorial, you will learn
– How to convert any type of data to date in Excel
– Convert text date to Date Format using DATEVALUE, DATE, LEFT, RIGHT, MID, SUBSTITUTE, TEXT, etc. functions
– Convert text or numeric date to Date Format using features like Paste Special, Find & Replace, and Text to Columns
– Convert numeric data to Date Format using functions DATE and TEXT functions and VBA
– Convert improper text to supported date format.

While writing this article, we applied all procedures using Microsoft 365, but they may be utilized with any Excel version.

Working with dates in Excel is very common for users. But sometimes dates don’t work properly due to improper formatting or errors in the formula. So it’s important to know the conversion of a number or a text to date.

Here is an overview of converting data to dates in Excel with various formulas.

Click the image for a detailed view

⏷Convert Text Date to Date Format in Excel
⏵Use DATEVALUE Function
⏵Apply VALUE Function
⏵Use Text to Column Wizard
⏵Use Paste Special Feature to Convert Data to Date
⏵Convert Detailed Text Date (Long Date) to Regular Date Format
⏵Use Mathematical Operations to Convert text Formatted Dates
⏵Convert 8-Digit Text to Date
⏵Fix Dates with Two-digit Years for Converting Text Date to Date Format
⏷Convert Date Values with Dots to Proper Dates
⏵Combine VALUE and SUBSTITUTE Functions
⏵Use Find and Replace Feature
⏷Convert Number to Date in Excel
⏵Use Custom Number Format
⏵Combine Multiple Functions to Convert 8-Digit Number to Date
⏵Use Text to Column Feature to Convert 8-Digit Number to Date
⏵Apply TEXT Function
⏵Use VBA to Convert Number to Date
⏷Convert Improper Text Formatted Date to Supported Date Format
⏵Convert Text Dates Containing Month Names
⏵Convert When Name of Days Is at the End of Dates
⏷Things to Remember
⏷Frequently Asked Questions
⏷Convert to Date in Excel: Knowledge Hub

How to Convert Text Date to Date Format in Excel?

There are 8 different ways to convert text-formatted date or date-to-date format in Excel. Choose any of them according to your preference.

1. Convert Text Date to Date Format Using the DATEVALUE Function

To convert a text-formatted date to a date, use the formula below containing the DATEVALUE function. This will convert the date to a number.

=DATEVALUE(C5)

The number is assigned by Excel to the corresponding date. We will convert this number to date again. To do this, select the range D5:D9 and then click on the drop-down icon in the Number group.
After that, select Short Date or Long Date to convert the number to date.

These dates can be used for calculation.

2. Applying the VALUE Function to Convert Text Data to Date

We can also find a similar outcome by using the VALUE function.

=VALUE(C5)

Format the numbers to date following the procedures described in the previous method.

Thus, you can convert texts to dates in Excel.

3. Convert Text Date to Date Format Using Text to Column Wizard

Select the range of the dates in text format and select Data >> Text to Columns.
The Convert Text to Columns Wizard dialog box will appear. Choose the option Delimited and click Next to go to step 2.

After that, select the Delimiter for text formatted dates. In this case, we select Comma.
Next, click Next.

Later, choose a proper Date format and destination cell.
Finally, click on the Finish button.

You will see the dates and days split, and the dates remain in the Date Format.

Note: Dates in the Text Format should contain dates first before the names of the days. Otherwise, the dates remain in text format and cannot be converted to dates for calculation.

4. Using Paste Special Feature to Convert Data to Date

The Paste Special feature can also be useful for converting text-formatted dates to supported date formats.

Select the range of dates in the text and press Ctrl + C to copy it.
Next, right-click on the cell where you want to store the dates and select Paste Special from the Context Menu.
After that, click Paste Special again.

In the Paste Special dialog box, select the option All to paste all data.
Also, choose the Add option from the Operation group.

You will see the numbers for the corresponding dates after this operation. Here, Excel adds the values of the text dates to zero and returns the number in the cell where the Context Menu was opened.

Now, select the range D5:D9 and then click on the drop-down icon in the Number group.
Select the Short Date option from the dropdown menu.

After converting the numbers to short dates, you will get the following output.

5. Converting Detailed Text Date (Long Date) to Regular Date Format

A detailed date, containing the names of the day and month, is stored as text. We can convert it to date format using the following formula.

=DATEVALUE(RIGHT(C5,LEN(C5)-FIND(" ",C5)))

Formula Breakdown

FIND(” “, C5): The FIND function searches for a space character (” “) within the text in cell C5. It returns the position of the first occurrence of the space character. This is assuming that the content in cell C5 represents a date in a format where the day, month, and year are separated by a space.
- Output: 9
LEN(C5): The LEN function calculates the length of the text in cell C5. It returns the total number of characters in the cell’s content.
- Output: 25
LEN(C5) – FIND(” “, C5): This part of the formula subtracts the position of the space character from the total length of the text in cell C5. It determines the number of characters from the space character until the end of the text. This effectively isolates the portion of the content that represents the year.
- Output: 16
RIGHT(C5, LEN(C5) – FIND(” “, C5)): The RIGHT function takes two arguments: a text string and the number of characters to extract from the right side of the text. In this case, it takes the text from cell C5 and extracts the number of characters determined in the previous step. This isolates the portion of the content that represents the year.
- Output: “04 February 2025”
DATEVALUE(RIGHT(C5, LEN(C5) – FIND(” “, C5))): The DATEVALUE function in Excel converts a text representation of a date into a numerical value that Excel recognizes as a date. In this formula, it takes the extracted year value and converts it into a date value.
- Output: 45692

Finally, format the resultant number to date for convenience.

6. Using Mathematical Operations to Convert text Formatted Dates

Here is another quick formula to convert the text formatted dates to date format. We use the Addition Operator (+) to force Excel to recognize the data as a date value.

=C5+0

7. Converting 8-Digit Text to Date

Sometimes, we store 8-digit dates with months in the front as text in an Excel sheet. To convert them to suitable dates, apply the formula below.

=DATE(RIGHT(C5,4),LEFT(C5,2),MID(C5,3,2))

Formula Breakdown

RIGHT(C5, 4): The RIGHT function extracts the rightmost four characters from cell C5. This assumes that the content in cell C5 represents a date in the format “MMDDYYYY” (e.g., “09142021“). The RIGHT function with the argument “4” extracts the last four characters, which represent the year.
- Output: 2021
LEFT(C5, 2): The LEFT function extracts the leftmost two characters from cell C5. It retrieves the first two characters of the content in cell C5, representing the month.
- Output: 09
MID(C5, 3, 2): The MID function extracts a specific number of characters from a text string, starting from a specified position. In this case, it extracts two characters from cell C5, starting from the third position. This retrieves the characters representing the day.
- Output: 14
DATE(RIGHT(C5, 4), LEFT(C5, 2), MID(C5, 3, 2)): The DATE function in Excel takes three arguments: year, month, and day. In this formula, the year is obtained by using the RIGHT function, the month by using the LEFT function, and the day by using the MID The DATE function then combines these values and returns a date value.
- Output: 9/14/2021

Thus, you can convert a text formatted date to date.

8. Fixing Dates with Two-digit Years for Converting Text Date to Date Format

Sometimes dates in two-digit year format return errors, and we need to fix them for further calculation with the dates. You can see some dates in two-digit year format in the picture below.

To fix the error, select the date range and click on the marked icon in the picture below.
You will see some options pop up. Select Convert XX to 20XX.

The command will convert the dates from a 2-digit year format to a 4-digit year format. But the dates will be stored as texts. So we apply the VALUE function to convert these text-formatted dates to corresponding numbers and later format them to dates from the Number ribbon group.

=VALUE(C5)

How to Convert Date Values with Dots to Proper Dates in Excel

Sometimes, dates can be expressed with dots, which are not supported by Excel for calculation. We can use the following 2 methods to convert these dates to the proper format in Excel.

1. Combining VALUE and SUBSTITUTE Functions

We can combine the VALUE and SUBSTITUTE functions to remove those dots and replace them with slashes or hyphens, which are supported by Excel.

=VALUE(SUBSTITUTE(C5,".","-"))

Format the numbers obtained by the formula to date.

2. Using Find and Replace Feature

You can also use the Find & Replace feature to convert date values with dots. This feature allows us to convert date values with dots to the proper format without using any formula.

To replace dots with slashes, select the dates with dots and press Ctrl + H. This will open the Find and Replace dialog box. Type dot “.” and “/” in Find what and Replace with sections respectively.
Next, click on the Replace All button.

Finally, all the dates with dots will be converted to standard date format.

How to Convert Number to Date in Excel?

There are 5 basic ways of converting numeric data to date. We discussed them in the following sections.

1. Using Custom Number Format to Convert Number to Date

It’s a common issue that dates are converted to numbers while working on the workbooks of other users. Here are some numbers in the following image, for example.

To convert these numbers to dates, select the number range C5:C9 and convert them to Long or Short Follow the image below to understand the topic clearly.

There are more date formats you may want to use. Here, I’ll show you a format containing days and month names.

Select the date range and press Ctrl + 1 to open the Format Cells dialog box.
In the Format Cells dialog box, select Custom and a date format with dddd, mmmm dd, yyyy. You can also type the modified version of this format in the Type section like dddd-dd mmm-yyyy or dd mmm yyyy, dddd.

The operation will return to the Long Date format.

2. Combining LEFT, MID, RIGHT, and DATE Functions to Convert 8-Digit Number to Date

Here, we convert the dates in 8-digit number format to date using the following formula.

=DATE(LEFT(C5,4),MID(C5,5,2),RIGHT(C5,2))

3. Using Text to Column Feature to Convert 8-Digit Number to Date

We can also use the Text to Column feature to convert dates in 8-digit number format to standard date format. For this purpose, select the date range with an 8-digit number format and follow the first 2 steps of the Text to Column Wizard method.

Here, uncheck all delimiters and click Next.

Next, choose the Date format, as our data is in YYYYMMDD format, we chose the YMD format.
After that, select a Destination cell where the data with dates will start.
Finally, finish the procedure by clicking the Finish button.

This operation will convert the 8-digit date format to the standard date format.

4. Using TEXT Function

We can also apply the TEXT function to convert the number to dates. However, this won’t return a number, so we can use the data of dates for calculation.

=TEXT(C5,"mm-dd-yyyy")

5. Using VBA to Convert Number to Date

VBA can also be a useful tool to convert numbers to date. Here is the procedure to apply the Macro.

Press Alt + F11 or select Developer >> Visual Basic to open the VBA window.

After that, click the Insert tab and select the Module option.

Insert the following code.

Sub Converting_Num_yyyymmdd_To_Date()
Dim my_numbr As Range
Dim my_selected_range As Range
On Error Resume Next
xTitleId = "Converting Number to Date"
Set my_selected_range = Application.Selection
Set my_selected_range = Application.InputBox("Range", _
xTitleId, my_selected_range.Address, Type:=8)
   For Each my_numbr In my_selected_range
       my_numbr.Value = DateSerial(Left(my_numbr.Value, 4), _
       Mid(my_numbr.Value, 5, 2), Right(my_numbr.Value, 2))
       my_numbr.NumberFormat = "mm/dd/yyyy"
   Next
End Sub

Next, go back to the sheet containing dates as numbers >> press Alt + F8 >> Run the code.

The code will open an InputBox and select the range of the numbers containing 8-digit dates.
Next, click OK.

After that, you will find out the numbers converted to dates.

How to Convert Improper Text Formatted Date to Supported Date Format?

If dates in your dataset are improperly formatted as texts, converting them to a supported date format can be tricky. The solutions will also vary based on the scenarios. Here, we will present two examples of converting an unconventional date format to a supported date format.

1. Converting Text Dates Containing Month Names

The following formula extracts the value of the date assigned by Excel from the text formatted date with month names.

=DATEVALUE(RIGHT(C5,2)&"-"&TEXT(MID(C5,6,3),"MMM")&"-"&LEFT(C5,4))

2. Converting When Name of Days Is at the End of Dates

The formula below extracts the value of the date assigned by Excel from the text formatted date with the name of days and months.

=DATEVALUE(MID(C5,10,2)&"-"&MID(C5,6,3)&"-"&LEFT(C5,4))

What Are the Things to Remember?

Excel recognizes different date formats, such as “MM/DD/YYYY,” “DD-MM-YYYY,” “YYYY/MM/DD”, etc. Ensure that the text you want to convert matches one of these standard date formats.
We commonly use the DATEVALUE function in Excel to convert a text representation of a date into a numeric date value that Excel can recognize. It is important to ensure that the text being converted is in a format that the DATEVALUE function can interpret correctly.
Excel’s interpretation of dates can be influenced by regional settings. The order of day, month, and year in a date format may vary depending on the regional settings of your computer. It’s important to double-check and adjust the settings if necessary to ensure accurate date conversions.
When converting text to dates, watch out for #VALUE! error. This error may occur if the text being converted does not match a recognized date format or if there are inconsistencies in the data.

Frequently Asked Questions

1. What’s the difference between Text Date vs. Regular Date in Excel?

Answer: Dates are stored as numerical values in Excel, allowing you to use them in calculations just like regular integers. This also allows you to format dates or order your data by date, which you cannot accomplish with text-based dates.

For example, Excel allows the date format 14-Feb-2023. If you write it into a cell, it will seem like a date, but Excel will record it as 44971, which corresponds to the date 14-Feb-2023.

However, because 14.Feb.2023 is not an acceptable date format, Excel will consider it as a text string. As a result, because it isn’t stored as a number, you can’t utilize it in calculations.

2. Is there a way to automatically convert dates in Excel as I enter them?

Answer: Yes, you can use the AutoCorrect feature in Excel. Go to File >> Options >> Proofing >> AutoCorrect options, and check the box for “Replace text as you type“. Enter the text format you want to replace with a date format, and Excel will automatically convert it as you type.

3. Why is the date format not changing in Excel?

Answer: Excel stores dates as sequential serial numbers. The imported dates are probably stored as a text value, instead of real numbers, and that is causing the formatting problem. Note that we can only format positive numbers as valid dates.

Download Practice Workbook

Convert to Date.xlsm

Conclusion

In the end, we can conclude that you will have the necessary ideas of how to convert any numbers or texts to dates and the applications of those features in Excel. This Excel tutorial covers various methods for converting different types of data into date formats. It includes techniques such as using functions like DATEVALUE, DATE, LEFT, RIGHT, MID, SUBSTITUTE, and TEXT to convert text dates.

Additionally, it explores methods like Paste Special, Find and Replace, and Text to Columns for converting text or numeric dates into the Date Format. Numeric data can be transformed into the Date Format using functions like DATE, TEXT, and VBA. If you have any ideas or suggestions, please share them in the comment section.

Convert to Date in Excel: Knowledge Hub

<< Go Back to Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!