Excel Help

[TraderJoe08]

i don't know anything about programming, but i think this is an easy fix.

Sub ConvertTimes()

'Converts times in the text format "809a" into Excel time format
'Processes all selected cells on the active worksheet.

Dim C As Range 'a cell
Dim HH As String
Dim MM As String
Dim L As Integer 'the string length

If TypeName(Selection) <> "Range" Then Exit Sub

For Each C In Selection.Cells
'Text string must end with A or P
If UCase(C.Value) Like "*[AP]" Then
L = Len(C.Value)
MM = Mid(C.Value, L - 2, 2)
HH = Left(C.Value, L - 3)
If HH = "12" Then HH = "0"
C.Value = (IIf(Right(UCase(C.Value), 1) = "P", CInt(HH) + 12, CInt(HH)) + CInt(MM) / 60) / 24#
C.NumberFormat = "h:mm;@"
End If
Next C

End Sub


my data doesn't have an "a" or "p" just need to convert 1245 to 12:45
data is already in military time

thanks i really appreciate answers

 

[DirkDigler]

Here's a real simple way to do it. If your times are in column A for example, put this in B1

=--(TEXT(A1,"00\:00"))

Then copy and paste as needed down the B column.

Oh and the B column should be formatted as hh:mm

 

[TraderJoe08]

thanks

I would like to have it converted into the same cell so i can then save the file as .txt and import it into a platform.

 

[TraderJoe08]

Quote from DirkDigler:

Here's a real simple way to do it. If your times are in column A for example, put this in B1

=--(TEXT(A1,"00\:00"))

Then copy and paste as needed down the B column.

Oh and the B column should be formatted as hh:mm

Ok it worked, now i have another issue lol

Thanks for your help

 

[DirkDigler]

Quote from TraderJoe08:

Ok it worked, now i have another issue lol

Thanks for your help

Hey no sweat. I'm by no means an Excel guru but if you want toss the new issue out there and I'll give it shot...