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 to Date Excel


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

=DATEVALUE(C5)

  • Apply the formula and AutoFill to the other cells.

Converting to Date by DATEVALUE Function

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

Formatting Values 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)

Converting to Date by VALUE Function

  • Format the numbers to dates.

Formatting Values to Date (2)


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.

Opening Text to Column Wizard Window

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

Choosing Delimiter for Text to Column Wizard

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

Choosing Date Format and Starting Cell

  • The dates and days split, and the dates remain in the Date Format.

Converted Date by Text to Column Feature

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.

Initiating Paste Special Feature

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

Selecting Proper Option from Paste Special Window

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

Output After Using Paste Special Tool

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

Formatting the Date As a Short Date

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

Converted Date by Paste Special Feature


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

Converting Text Date with Day to Short Date

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

Converting Date by Mathematical Operation


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

Converting 8 Digit Text Date to Short Date

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.

Dates with Two Digit Year and Error

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

Setting the Year Format

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

Dates with Two Digit Year Converted to Short Date Format


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,".","-"))

Converting to Date by SUBSTITUTE Function

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

Converting to Date by Find and Replace Feature

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

Converted Date Format from Find and Replace Feature


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.

Dates in Number Format

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

Converting Number to 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.

Applying More Date Format

  • The operation will return the Long Date format.

Long Date Format

Read More: How to Convert General Format to Date in Excel


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

Using Formula to Convert 8 Digit Number (YYYYMMDD) to Date


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.

Unchecking All Delimiters and Going to Step 2 of Text to Column Wizard

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

Setting up Date Format and Destination of Numbered Dates

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

8 Digit Number (yyyymmdd) Formatted to Date


Method 4 – Using the TEXT Function

Use the following function:

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

Use of TEXT Function to Convert to Text Date

Read More: How to Convert Serial Number to Date in Excel


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.

Opening VBA Window

  • Click the Insert tab and select the Module option.

Opening VBA Module

  • 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

VBA Code to Convert Number to Date

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

Opening Macro Window

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

Selecting Range for the Macro InputBox

  • You will find out the numbers converted to dates.

Number Formatted Date Converted to Date Format

Read More: How to Convert Number (YYYYMMDD) to Date Format in Excel


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

Date with Month Name to Date

Read More: How to Convert Text to Date and Time in Excel


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

Date with Day Name to Date

Read More: Text Won’t Convert to Date in Excel


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 in Excel: Knowledge Hub


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

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

2 Comments
  1. Thanks for this wonderful documentation which saved my day.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo