How to convert date format to general format?

September 25, 2023

I’m trying to convert the format of cells in column N from Date to General. Some cells get converted but others do not. tried with the Delimited option but it still doesn’t change all the cells. Eventually tried this loop to loop through each cell and try to change the cells’ formatting, but needs a tweak to get it to work. any support please to achieve that?

Dim Lrow As Long: Lrow = Sheets("test").Range("N" & Sheets("test").Rows.Count).End(xlUp).Row
Dim rg As Range: Set rg = Sheets("test").Range("N1:N" & Lrow)
Dim rCount As Long: rCount = rg.Rows.Count
Dim arr(): arr = rg.EntireRow.Columns(14).Value2
Dim v As Long

For v = 1 To rCount

arr(v, 1) = Format(Cells(v, 14).Value, "General") <------- this line needs a modification 

Next v
rg.EntireRow.Columns(14).Value = arr

>Solution :

Looping through all cells takes lots of time. Below code set the format for column N (index is 14).

Sub demo()
    Dim Sht As Worksheet
    Set Sht = ActiveSheet
    Sht.Columns(14).NumberFormatLocal = "General"
    With Application.Intersect(Sht.UsedRange, Sht.Columns(14))
        .Formula = .Formula 
    End With
End Sub

If you don’t have to keep any format on column N, clear is the best method to reset the cell format.

Sub demo()
    Dim Sht As Worksheet, Rng As Range
    Dim arr
    Set Sht = ActiveSheet
    Set Rng = Application.Intersect(Sht.UsedRange, Sht.Columns(14))
    arr = Rng.Formula
    Sht.Columns(14).Clear
    Rng.Formula = arr
End Sub