Changing date formats in Excel using

Question

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?

Answer 1

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.

Edit after discussing in chat:

The screen shot you posted in chat is this:

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.

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:

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.

Answer 2

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