Change date format in Excel using

Question

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

To do this, I tried to use the following line inside the macro, but instead changed the cell data as "### ..... #".

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

Can someone tell me why this is happening? Is there any other way to do this?

Answer 1

With all the good answers, I will add a 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

Answer 2

If the date / time cell is filled with #, this means the column is too narrow to display the format.

.

. . , . B .

:

, , :

"", , - . , , Excel .

Excel , 1 1/1/1900. , , - , Excel.

20150930 , Excel Sep-30-2015. Excel 42277, .

, "" , : ##### - , , Excel .

Excel, . "date" A1

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

, Excel Sep-30-2015 :

, # , , , Excel.

Answer 3

, .

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