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

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

⏷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

Method 1 – Convert Text Date to Date Format Using the DATEVALUE Function

Use the formula below containing the DATEVALUE function:

=DATEVALUE(C5)

Apply the formula and AutoFill to the other cells.

Excel returns the dates in serial number format, so they need to be converted.
Select the range D5:D9 and click on the drop-down icon in the Number group.
Select Short Date or Long Date to convert the number to date.

Method 2 – Applying the VALUE Function to Convert Text Data to Dates

Follow Method 1 but replace the formula with the VALUE function.

=VALUE(C5)

Format the numbers to dates.

Method 3 – Convert a Text Date to Date Format Using the Text to Column Wizard

Select the range of the dates in text format, go to Data, and choose 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.

Select the Delimiter for text formatted dates. In this case, we selected Comma.
Click Next.

Choose a proper Date format and destination cell.
Click on the Finish button.

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.

Method 4 – Using the Paste Special Feature to Convert Data to Date

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

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

Excel adds the values of the text dates to zero and returns the number in the cell where the Context Menu was opened.

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.

Method 5 – Converting a Detailed Text Date (Long Date) to the Regular Date Format

Use 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

Method 6 – Using Mathematical Operations to Convert Text Formatted Dates

Here is another quick formula to convert the textual dates to date format:

=C5+0

Method 7 – Converting an 8-Digit Text to Date

We can store 8-digit dates with months in the front as text in an Excel sheet.

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

Method 8 – Fixing Dates with Two-Digit Years for Converting Text Date to Date Format

You can see some dates in two-digit year format in the picture below.

Select the date range and click on the exclamation icon.
You will see some options pop up. Select Convert XX to 20XX.

The dates will be stored as texts. Apply the VALUE function to convert these text-formatted dates to corresponding numbers:

=VALUE(C5)

Format them to dates from the Number ribbon group.

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

Method 1 – Combining VALUE and SUBSTITUTE Functions

Use the following function to replace the periods into dashes so they can be converted:

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

Format the numbers obtained by the formula to date.

Method 2 – Using the Find and Replace Feature

Select the dates with dots and press Ctrl + H. This will open the Find and Replace dialog box.
Type “.” and “/” in Find what and Replace with boxes, respectively.
Click on the Replace All button.

All the dates with dots will be converted to standard date format.

How to Convert a Number to a Date in Excel

Method 1 – Using a Custom Number Format to Convert a Number to a Date

Here are some numbers in the following image, which is how Excel normally stores dates.

Select the number range C5:C9 and convert them to Long Date or Short Date.

We can also use advanced formats:

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 the Long Date format.

Method 2 – Combining LEFT, MID, RIGHT, and DATE Functions to Convert an 8-Digit Number to a Date

Use the following formula:

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

Method 3 – Using the Text to Column Feature to Convert 8-Digit Number to Date

Follow the initial steps of the Text to Column Wizard method.

Uncheck all delimiters and click Next.

Choose the Date format. Since our data is in YYYYMMDD format, we chose the YMD format.
Select a Destination cell where the data with dates will start.
Click the Finish button.

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

Method 4 – Using the TEXT Function

Use the following function:

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

Method 5 – Using VBA to Convert a Number to a Date

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

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

Save the code and go back to the sheet containing dates as numbers.
Press Alt + F8 and Run the code.

The code will open an InputBox. Select the range of the numbers containing 8-digit dates.
Click OK.

You will find out the numbers converted to dates.

How to Convert an Improper Text Formatted Date to a Supported Date Format

Method 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))

Method 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))

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

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

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. Because it isn’t stored as a number, you can’t utilize it in calculations.

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

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.

Why is the date format not changing in Excel?

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 the Practice Workbook

Convert to Date.xlsm

Convert to Date in Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!