Forum Discussion
Formatting time
I am doing a time study and have some date time data that was given to me in a format where the time does not have a colon in it so formatting the cells is not working. Is there a way to easi...
Assuming your posted example data is representative of all your data, then I think this formula should work...
=0+REPLACE(A1,12,0,":")
Format the cell with the Custom Format of your choosing.
Rick_Rothstein wrote:
Assuming your posted example data is representative of all your data, then I think this formula should work...
=0+REPLACE(A1,12,0,":")
Format the cell with the Custom Format of your choosing.
If you wanted to fix your data in place, you could use this macro...
Sub FixDateTime()
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
.Value = Evaluate("IF({1},REPLACE(" & .Address & ",12,0,"":""))")
End With
End Sub
Note: If you have a header in cell A1, then change the "A1" to "A2" in the With statement.