CODEWITHSUNDEEP
vbaexceldate
Madhusudan
Madhusudan

Reputation: 4815

Changing date formats in Excel using

I have a excel sheet in which a column has date date in the format "yyyyMMdd" and I want to format it as "yyyy/MM/dd".

For this I tried to use following line inside macro, but it's converting cell data as "###.....#" instead of changing date format.

Sheet1.Range("C3", "C302").NumberFormat = "yyyy/mm/dd"
...
result = "#####...#"
...

Can someone tell me why it's happening? Is there any other way for doing this?

Upvotes: 4

Views: 976

Answers (3)

0m3r
0m3r

Reputation: 12499

With all the good answers, I will add simple vba solution...

Option Explicit
Sub FormatDate()
    Dim xlRng As Range
    Dim xlShtRng As Range

    '//- Date format 20160112
    Set xlShtRng = [A3:A10] '//- or [A3, A6, A10]

    For Each xlRng In xlShtRng
        xlRng.Value = DateSerial(Left(xlRng.Value, 4), Mid(xlRng.Value, 5, 2), Right(xlRng.Value, 2))
        xlRng.NumberFormat = "yyyy/mm/dd" '//- 2016/01/12
    Next
End Sub

Upvotes: 3

Linga
Linga

Reputation: 955

Please try this..

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

Upvotes: 2

teylyn
teylyn

Reputation: 35900

If a date/time cell appears full of # signs, it means that the column is too narrow to display the format.

Make the column wider to accommodate the full width of the selected date format.

See this screenshot. Both columns have the same format. Column A is too narrow to show the dates. Column B is wide enough.

enter image description here

Edit after discussing in chat:

The screen shot you posted in chat is this: enter image description here

The "dates" you are referring to are not dates. They are numbers that are way higher than what Excel uses for dates in this millenium.

Excel stores dates as whole numbers, starting as 1 for 1/1/1900. What you show in your screenshot are numbers way higher than Excel dates.

enter image description here

Your number 20150930 is NOT what Excel considers Sep-30-2015. For Excel, that date would be the number 42277, which you can perfectly format as that date.

The reason that your "dates" formatted with your format string come out as ##### is that the numbers are way higher than what Excel can interpret as dates.

You will need to convert your numbers to real Excel dates, which you can do with a simple formula. With your first "date" number in cell A1, you can use the formula

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

to return a value that Excel regards as a true date for Sep-30-2015 in this screenshot:

enter image description here

So, the reason for all the # signs is that the numbers you are trying to format as dates are too big for dates in Excel's algorithms.

Upvotes: 4

Related Questions