Cell Format Difference

[AlexanderBB]

A Column in my worksheet holds dates
Format Cells - Number - Category is set to Custom and Type is dd mmm yyyy

Most of the entries in this column display like this in the Formula bar, or if read with VBA.
But some of them are in format dd/mm/yyyy.

All are dd mmm yyyy looking at the worksheet and right click "Format Cells" settings are the same.
Is there a reason for the difference and any suggestions for a 'fix' ? Thank you.

 

[StephenCrump]

That would be ok ?

You're best placed to test it. It's your data.

But it will be quicker not to loop:

Sub CustomToText()
    
    With Range("V2:V18609")
        .NumberFormat = "@"
        .Value = Evaluate("Text(" & .Address & ",""dd mmm yyyy"")")
    End With
    
End Sub

 

[StephenCrump]

If cl.NumberFormat = "dd mmm yyyy" ....

If you decide you do want numeric dates, then perhaps:

Sub CustomToDate()
    
    With Range("V2:V18609")
        .NumberFormat = "dd mmm yyyy"
        .Value = Evaluate("Datevalue(" & .Address & ")")
    End With
    
End Sub

 

[AlexanderBB]

Good to know. I had this weird idea if the cell wasn't cleared the new format would not be applied.