0

I am importing data from a text file and using a macro to format in Excel. It recognizes and formats time (hh:mm:ss) - except when it's e.g. 00:15:27 - the data drops the 0's for hh completely and my macro then recognizes the mm:ss as hh:mm. I track data for 24 hours - so when it's midnight I lose the hour in the data and might have a funky hour of 53:02:00 for example, or I could have 10:43:00 - thinking it's 10 o'clock and not 00:10:43.

Any way to get the macro to recognize there should be something there and change it to OO?

Thanks.

Improve this question
2
  • 3
    What does the macro look like?
    – fixer1234
    May 24, 2019 at 21:34
  • Is the process of Importing text file part of the macro or is it separate from the macro processing? If separate, you may also want to check how the import is happening and if anything can be debugged at that level before coming to macro.
    – patkim
    May 24, 2019 at 23:58

1 Answer 1

Reset to default
0

Say we have text values in A1 through A10 like:

enter image description here

and we want all the 5-character data to be treated like they have 00: in front of them. We can test for length:

Sub MakeTime()
    Dim r As Range, v As String
    For Each r In Range("A1:A10")
        v = r.Text
        If Len(v) = 5 Then v = "00:" & v
        r.Value = TimeValue(v)
    Next r
End Sub

The result:

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .