Scatted military time entries missing "0" Digits in CSV

[trentdowd]

Hi Guys,
The goal is to have one clean row of Time entries.
After many attempts at trying to solve this problem I am now here seeing if anyone else has some bright ideas.

Very simple problem that needs a fresh solution if there exists. I have CSV with thousands of faulty time entries that are missing a 0 or two messing up how they will be viewed. All is not lost though as there is a pattern to the missing zeros- that being any Zero to the left of the right digit in HH/MM/SS format has been erased.

better explaining - the fault happens when time entry should say - 19:32:06

but in the CSV it will be reproduced as 19:32:6

Or 00:06:02

but in the CSV it will be reproduced as 0:6:2

This problem is further exacerbated by the fact that the faulty entries are scattered across the sheet.

My only method successful method is splitting the entries into different cells but I would still have to manually move each faulty number into the correct column and then fill column with the missing zero one by one.

I then thought my main problem in this solution i have created is not being able to highlight the correct cells that need simply shifting to the right. If I could do this easily then shifting them at the same time... would save time, then i could flash fill the appropriate cells with zeros. but this has proved unsuccessful.

I did try kutools reverse selection feature, but that did not work for what i'm trying to do.
Can someone point me in the right direction or provide help to solve this conundrum, failing a new solution I will have to manually, carefully sort the CSV and that might be a few days work at best. I have provided a file for anyone to mess about with if you need it-

Many thanks

timesortout.xlsx

drive.google.com

 

[GlennUK]

This will covert the text times to real times, correcting the error, if that is any use:

=TRIM(LEFT(A2,FIND(":",A2)-1))/24+TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(":",A2)+1,99),":",REPT(" ",100)),100))/(24*60)+TRIM(RIGHT(SUBSTITUTE(MID(A2,FIND(":",A2)+1,99),":",REPT(" ",100)),100))/(24*60*60)

... format as time.

 

[trentdowd]

This will covert the text times to real times, correcting the error, if that is any use:

=TRIM(LEFT(A2,FIND(":",A2)-1))/24+TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(":",A2)+1,99),":",REPT(" ",100)),100))/(24*60)+TRIM(RIGHT(SUBSTITUTE(MID(A2,FIND(":",A2)+1,99),":",REPT(" ",100)),100))/(24*60*60)

... format as time.

Hi Glen, thanks for your reply, i tried this, but i don't think is what i'm looking for and it also resulted in incorrect time-

13:27:3 converted to 0.53712 and then changing cell back to time resulted in 12:53:27.

Because there is a digit missing/out of place, i don't think there will be any formula that will correct it perfectly n that fashion.

 

[lrobbo314]

Does the TimeValue() function do what you're looking for?

 

[sandy666]

is that what you want?

{part of data)

Time	Time
12:53:27	12:53:27
13:24:51	13:24:51
13:24:52	13:24:52
13:27:3	13:27:03
13:27:3	13:27:03
13:27:3	13:27:03
14:5:30	14:05:30
14:5:30	14:05:30
14:5:31	14:05:31
14:8:55	14:08:55
14:20:5	14:20:05
14:20:5	14:20:05
14:20:6	14:20:06
14:20:6	14:20:06
14:20:6	14:20:06
14:20:6	14:20:06
14:20:6	14:20:06
14:20:7	14:20:07

 

[trentdowd]

is that what you want?

{part of data)

Time	Time
12:53:27	12:53:27
13:24:51	13:24:51
13:24:52	13:24:52
13:27:3	13:27:03
13:27:3	13:27:03
13:27:3	13:27:03
14:5:30	14:05:30
14:5:30	14:05:30
14:5:31	14:05:31
14:8:55	14:08:55
14:20:5	14:20:05
14:20:5	14:20:05
14:20:6	14:20:06
14:20:6	14:20:06
14:20:6	14:20:06
14:20:6	14:20:06
14:20:6	14:20:06
14:20:7	14:20:07

Oh wow that is amazing....I've spent the whole of today trying to figure out a solution, please share how that was accomplished sandy...

 

[sandy666]

I assumed you want change blue time to green time
so use Power Query (Get&Transform)

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}})
in
    #"Changed Type"

where Table1 is a blue table (column)

Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.

Store photos and docs online. Access them from any PC, Mac or phone. Create and work together on Word, Excel or PowerPoint documents.

1drv.ms

 

[trentdowd]

Oohh, Power Query, the advanced side of excel, I've never used it,(currently watching this -

) I will spend some time getting acquainted with it then report back if I've successfully used you code to make things work. Thanks so much!

 

[sandy666]

find:
Data - From Table
then Close&Load

for future:
more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, higher versions has PQ built-in
• General: Excel Help

 

[GlennUK]

Hi Glen, thanks for your reply, i tried this, but i don't think is what i'm looking for and it also resulted in incorrect time-

13:27:3 converted to 0.53712 and then changing cell back to time resulted in 12:53:27.

Because there is a digit missing/out of place, i don't think there will be any formula that will correct it perfectly n that fashion.

... the only way that "error" occurred is if you were pointing at the wrong cell. Your formula must have been pointing at a cell with 12:53:27 (i.e. your very first date example), and not at the cell with 13:27:3