CODEWITHSUNDEEP
excel
Doug Fir
Doug Fir

Reputation: 21204

Dates Formatting in Excel

I have a column with many thousands of rows. The column is meant to be date and is of the format dd/mm/yyyy

But, when I try to do formulas based on the dates, something is clearly amiss.

For example, if you try to apply autofilter on the dates, some of them are grouped as a year with the expandable boxes while others appear as their own items.

For each record I tried a formula to parse it apart.=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

That did not help.

I also selected the column and switched it from general to date format

I really don't know how to ask the question any clearer. I can tell you that with a date of the format 1/11/2013 when I run =year(right(A1,4)) I get 1903 instead of 2013. When I run =date(right(A1,4),mid(A1,3,2),left(A1,2)) the formula returns 2/10/3192

Upvotes: 1

Views: 2905

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

It's very simple why your formulas doesn't work corectly. When yor're using somehting like this: RIGHT(A2,4), your value from A2 translates to 41579 for 1/11/2013 (Excel stores all dates as integers and all times as decimal fractions. You can read more here). Next formula should work well:

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

Btw, if you'd like to get correct format for dates, you can add formula in some empty column (but before set date format for this column):

=A2*1

and drag it down. Then copy values from this temp column and paste them using "Paste special->Values" in colunm A (where should be date format as well)

enter image description here

Or you can use this simple macro:

Sub test()
    With Range("A2:A100")
        .NumberFormat = "dd/mm/yyyy"
        .Value = .Value
    End With
End Sub

Upvotes: 3

Related Questions