Convert Excel String To Date – VBA Date Format

Convert Text To Date in Excel or Number

For Excel date format conversion manual or VBA follow these 2 methods.

Excel automatically converts the data types to its correct format most times. Also it allows User to change it to any desired format if they want (by right clicking the cell and choose “Format Cells” option or CTRL+1).

These format Cells option does not work as desired sometimes. Because we misunderstand it’s purpose. This option will not change the actual data stored at the background, instead it only changes the way Excel displays the data format.

To correct or change the actual data itself, “Text to Columns” option or VBA format date option has to be used.

Excel Convert String to Date Format

Consider you have a number or date value stored in a worksheet in string format.Few examples are given in below image.

In this article we are going to use Text to columns option to convert text to date in Excel.

How to Convert Excel String To Date?

To convert number stored in string format back to number or to do a Excel string to date conversion, follow these steps.

1. Select the data or range to convert to Date or number format.
2. Click Menu. Choose ‘Data Tab’ -> ‘Text to Columns’ option.
3. Choose “Fixed Width” in pop up menu.
4. Click Next till this list of options appears.
   • General
   • Text
   • Date (MDY, DMY, YDM, MYD, DYM, YMD)
5. Select ‘General’ if data is number.
6. Select ‘Date’, If source data is a date.
7. Click Ok to complete format conversion.

Date will default to mm/dd/yyyy format. If you want a different date format,then choose other options near the Date option.

Note: VBA Date format conversion Formula in Excel.

Excel VBA Convert String to Date or Number

The below VBA code is a direct macro recorded while doing the above Text to Columns menu option. It can be used to perform VBA string to date conversion in any automation.

Enter any date value in a worksheet in the range C2 – C6. Then press Alt + F11 & copy paste the below code to the VB editor. Press F5 to see the actual conversion happening automatically.

Sub VBA_String_To_Date()
'
' Date and Numbers Format Convertion
'
' To Convert Numbers stored in String Format to Number Format
    Range("C2:C4").TextToColumns Destination:=Range("D2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

' To Convert Date in MDY format stored as String
    Range("C5").TextToColumns Destination:=Range("D5"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 3), TrailingMinusNumbers:=True

' To Convert Date in YMD format stored as String
    Range("C6").TextToColumns Destination:=Range("D6"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True
End Sub

This code can be used in your Automation code if you want to convert string to date or change VBA date format to number, but edit the range accordingly before using it. (The above code will only take date from the active worksheet.)