Help me convert a column of times from text, but starting like this (thank you)

[thankyou]

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]754
[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1007[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1102
[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]806[/TD]
[/TR]
</tbody>[/TABLE]


I've hand tallied a bunch of data (as text format), but to save time, I didn't enter the ":" (notice I didn't say "enter the colon"...wait I just did). Joking aside, I was wondering if you could help me convert that style to time format, exactly as above, but with these criteria:

time format
adding back the colon
no seconds
no am/pm
Change times to 24h

I hope this question isn't too boring, and thanks again for your help.

 

[Weazel]

I'm not sure I understand exactly but maybe something like ?

formatted as [h]:mm

Excel 2012

	A	B

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]754[/TD]
[TD="align: right"]7:54[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]10:07[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1102[/TD]
[TD="align: right"]11:02[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]806[/TD]
[TD="align: right"]8:06
[/TD]

</tbody>

Sheet1
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=0+TEXT(A2,"00\:00")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[thankyou]

Thoroughly helpful! huge thanks...now if I could just get that column to 24h format, so I can get my "1"s to "13"s. Appreciate you.

 

[Rick Rothstein]

Assuming your "time" values are in Column A starting at Row 1, does this macro produce the result you want (note... it physically changes your data cells in place)...

Sub FixTimeValues()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF(LEN(@),0+REPLACE(@,LEN(@)-1,0,"":""),"""")", "@", .Address))
    .NumberFormat = "h:mm"
  End With
End Sub

 

[Weazel]

i'm a little confused on the whole 24h question....

are all the times supposed to be 12 hours later than they are displayed?

if that's true then you can try something like =0.5+TEXT(A2,"00\:00")

 

[thankyou]

Weazel,

I was just writing in the following and hit return, but I had my tab on "protect", which I think get my browser from submitting, since requires redirect...then your post came thru. Bottom line: no worries on the 24h item. I wrote:

Kindly disregard my part re 24h. I was able to go through my small sample data and just convert the few manually. Thanks for all your help! It made a big difference.

Still getting my hang on things around here. Patience from awesome contributors like yourself and Rick make all the difference and make this site awesome. Rick, thank you so much. I will try that now, for a learning point and follow up shortly.

 

[Weazel]

you're very welcome

 

[thankyou]

ok, rick, I think macros aren't for me. they used to be, but I've just lost the patience. I think I spent about 30-45mins on this, so I had to give up. I've run out of time. I remember I used to run them reluctantly, but I think you just have to be versatile in them with shortcuts etc. I just don't have the patience. I remember in the past, when I did run them occasionally, I had the same bad feeling, where just getting to copying and pasting the **** thing lol was a royal pain. I think my brain just likes the other excel stuff. Thanks though. Doesn't mean it won't help others. Well, I have to get back to work, thanks again Weazel.