Home

Need Help Convert Text to Time Format

%3CLINGO-SUB%20id%3D%22lingo-sub-807189%22%20slang%3D%22en-US%22%3ENeed%20Help%20Convert%20Text%20to%20Time%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-807189%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%20data%20that%20I%E2%80%99m%20importing%20into%20a%20spreadsheet.%26nbsp%3B%20I%20need%20to%20reformat%20the%20data%20into%20a%20specific%20time%20format.%26nbsp%3B%20However%2C%20the%20source%20data%20can%20be%20in%20different%20formats%20so%20I%20need%20a%20VBA%20script%20to%20handle%20all%20the%20cases.%3C%2FP%3E%3CP%3EI%20decided%20to%20first%20import%20the%20data%20and%20change%20the%20format%20of%20the%20column%20to%20text.%26nbsp%3B%20I%20need%20each%20cell%20to%20be%20in%20the%20following%20format%20%E2%80%93%2000%3A00%3A00%3C%2FP%3E%3CP%3EThese%20are%20examples%20of%20the%20source%20data%3A%3C%2FP%3E%3COL%3E%3CLI%3ENull%3C%2FLI%3E%3CLI%3E%3A08%3C%2FLI%3E%3CLI%3E2%3A09%3C%2FLI%3E%3CLI%3E10%3A17%3C%2FLI%3E%3C%2FOL%3E%3CP%3EI%20wrote%20the%20following%20Sub%20that%20can%20handle%20converting%20options%201%2C%202%2C%20and%203.%26nbsp%3B%20But%20what%20I%20need%20is%20a%20way%20to%20also%20handle%20option%204%20without%20breaking%20the%20format.%3C%2FP%3E%3CP%3E%E2%80%98Start%20of%20Script%3C%2FP%3E%3CP%3ESub%20ReFormatDate()%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20Each%20Cell%20In%20Range(%22A2%3AA30%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20Cell.Value%20%3D%20%22%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cell.Value%20%3D%20%2200%3A00%3A00%22%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Else%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cell.Value%20%3D%20%2200%3A0%22%20%26amp%3B%20Cell.Value%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%20Cell%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%E2%80%98End%20of%20Script%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20run%20this%20script%2C%20if%20it%20runs%20into%20a%20cell%20like%20option%204%20it%20will%20incorrectly%20add%20an%20extra%200%2C%20returning%20a%20result%20like%20this%3A%2000%3A010%3A17.%26nbsp%3B%26nbsp%3B%20So%20I%20just%20need%20a%20way%20to%20be%20able%20to%20handle%20this%20option%20without%20breaking%20any%20of%20the%20other%20options.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20it%20would%20help%20if%20I%20could%20then%20reformat%20the%20entire%20column%20from%20text%20into%20the%20following%20time%20format%20as%20selected%20from%20Format%20Cells%20-%26gt%3BNumber-%26gt%3BTime-%26gt%3BType-%26gt%3B%26nbsp%3B%2037%3A30%3A55%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-807189%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
merclord
Occasional Visitor

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:

  1. Null
  2. :08
  3. 2:09
  4. 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies