CODEWITHSUNDEEP
excel
kunz
kunz

Reputation: 1047

setting out date and months and years in excel

I have a set of data which needs to be in date format but looks like 28041980 but i need it to be in date format like d/m/yyyy can this be done

enter image description here

Upvotes: 1

Views: 52

Answers (4)

Mark Ransom
Mark Ransom

Reputation: 308121

Once you get the date as a proper Excel serial number, you can change the formatting to get any display you want. There's a Date function that takes individual year, month, and day values to give an actual date. All you need is a little math to pick apart the individual parts.

=DATE(MOD(A1,10000),MOD(TRUNC(A1/10000),100),TRUNC(A1/1000000))

Upvotes: 1

user4039065
user4039065

Reputation:

You need to first get all of the numerical values into a common format that can be converted. In an unused column to the right use,

=RIGHT("00000000"&A1, 8)

Fill down to the extent of the column and then copy, paste special value to remove the formulas.

Select this new column of values and use Data ► Data Tools ► Text to Columns. Choose Fixed Width ► Next ► Next then select Date, DMY and Finish.

In VBA like the following.

Sub date_convert()
    Dim cl As Long

    cl = 1  'column A

    With Worksheets("Sheet2")
        With .Range(.Cells(2, cl), .Cells(Rows.Count, cl).End(xlUp))
            .Offset(0, 1).EntireColumn.Insert
            With .Offset(0, 1)
                .FormulaR1C1 = "=RIGHT(""00000000""&RC[-1], 8)"
                .NumberFormat = "@"
                .Value = .Value2
                .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, _
                               FieldInfo:=Array(0, 4)
                .NumberFormat = "dd/mm/yyyy"
            End With
            'optionally remove original column
            '.entirecolumn.delete
        End With
    End With
End Sub

Upvotes: 0

RocketTwitch
RocketTwitch

Reputation: 295

This will account for the leading day to be either 1 or 2 characters long. But it will only work assuming D/M/Y

A1 = 28041980
A2 = 3091970

   B1 =REPLACE(REPLACE(A1,LEN(A1)-3,0,"/"),LEN(A1)-5,0,"/")

Upvotes: 1

DarkAjax
DarkAjax

Reputation: 16223

You could try using TEXT and DATE along with substrings (RIGHT, MID and LEFT), like:

=TEXT(DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2)), "dd/mm/yyyy")

Upvotes: 0

Related Questions