CODEWITHSUNDEEP
excel
Krishantha de silva
Krishantha de silva

Reputation: 121

Convert number into date

There is a column in Excel with following data.

    15052015  
    19012014 
    13022013

I want to get that column data in date type (mm/dd/yyyy) and need to have following results:

05/15/2015
01/19/2014
02/13/2013

Upvotes: 2

Views: 160

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

Try like this:

=(MID(A1,3,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4))

You can try to format your data by doing some manipulation on the cell and adding this formula in your cell.

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

Select the column of numbers and choose Data ► Data Tools ► Text to Columns. Opt for 🔘 Fixed Width, Next. Then Next. Then for the Column data format opt for Date and choose DMY. Click Finish.

In VBA, the Range.TextToColumns method command can make quick work of this.

Sub Convert_DMY()
    With Worksheets("Sheet1")   '<~~ set this properly!
        With .Columns(1)        '<~~ set this properly!
            .TextToColumns Destination:=.Cells(1), _
                                DataType:=xlFixedWidth, _
                                FieldInfo:=Array(0, xlDMYFormat)  'xlDMYFormat = 4
        End With
    End With
End Sub

The xlDMYFormat comes from the xlColumnDataType enumeration set.

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

If you want a true Excel date, do this:

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

Upvotes: 2

Related Questions