I have a column of data that I’m importing into a spreadsheet. I need to reformat the data into a specific time format. However, the source data can be in different formats so I need a VBA script to handle all the cases.
I decided to first import the data and change the format of the column to text. I need each cell to be in the following format – 00:00:00
These are examples of the source data:
- Null
- :08
- 2:09
- 10:17
I wrote the following Sub that can handle converting options 1, 2, and 3. But what I need is a way to also handle option 4 without breaking the format.
‘Start of Script
Sub ReFormatDate()
For Each Cell In Range("A2:A30")
If Cell.Value = "" Then
Cell.Value = "00:00:00"
Else
Cell.Value = "00:0" & Cell.Value
End If
Next Cell
End Sub
‘End of Script
When I run this script, if it runs into a cell like option 4 it will incorrectly add an extra 0, returning a result like this: 00:010:17. So I just need a way to be able to handle this option without breaking any of the other options.
Also, it would help if I could then reformat the entire column from text into the following time format as selected from Format Cells ->Number->Time->Type-> 37:30:55
