Time conversion

[daverunt]

I'd like to convert a time string. I'm using a macro to extract the string from a text file and need to modify the string format within the macro.
Modifying the target cell to a time format is not possible.

hhh:mm:ss - this is always the number of characters
to text format that excludes empty values

For example 000:05:00
Would output 5m

001:10:08
Would output 1h 10m 8s



Any help appreciated.

 

[Rick Rothstein]

If the hours will always be less than 100...

=TEXT(0+MID(A1,2,10),"[h]\h m\m s\s")

 

[daverunt]

If the hours will always be less than 100...

=TEXT(0+MID(A1,2,10),"[h]\h m\m s\s")

Hi Rick,

thanks for that but that's a worksheet entry is it not? I am going to need to get it in a macro variable.

 

[johnnyL]

TimeString = Evaluate("TEXT(0+MID(A1,2,10),""[h]\h m\m s\s"")")

 

[daverunt]

I'll give that a try in the morning.
Thanks Johnny.

 

[daverunt]

I'll give that a try in the morning.
Thanks Johnny.

Sorry this didn't work. The time value e.g. 001:01:01 is in a string variable and needs to be converted and held in another string variable as 1h 1m 1s. Nothing is stored in a cell or can be stored in a cell.

 

[Rick Rothstein]

Give this function (which you can call from within your macro code) a try. Just pass in a variable containing your specialized time string or the quoted time string itself and the function will return the formatted time string.

Function FTime(ByVal S As String) As String
  Dim Arr As Variant
  Arr = Split(S, ":")
  FTime = Application.Trim(IIf(Arr(0), Arr(0) & "h ", "") & IIf(Arr(1), Arr(1) & "m ", "") & IIf(Arr(2), Arr(2) & "s", ""))
End Function

Note: I named the function FTime which stands for "Formatted Time".

 

[daverunt]

Cheers Rick. That's awesome.

I added a bit of code found here to remove the leading zero from each arr.

how to remove leading zeroes from a string

I have to extract the integer value from a string. Its actually an amount field. Say string can be 000000000000512 or 0000040000000 I want only the integer value from this string i.e.; 512/ 40000000

stackoverflow.com

I understand what they did here doing a left trim of spaces and replacing any zeros converted to a space on the right back to zero. Clever.
09m 59s comes out as 9m 59s, which is my preferred output.

• replace the zeros with spaces
• left trim
• replace the spaces with zeros

FTime = Application.Trim(IIf(Arr(0), Replace(LTrim(Replace(Arr(0), "0", " ")), " ", "0") & "h ", "") & IIf(Arr(1), Replace(LTrim(Replace(Arr(1), "0", " ")), " ", "0") & "m ", "") & IIf(Arr(2), Replace(LTrim(Replace(Arr(2), "0", " ")), " ", "0") & "s", ""))

Thanks for your time.

 

[Rick Rothstein]

Whoops! I forgot about the leading zeroes (it was 4:30 in the moring when I posted that function). Here is my code modified to remove them...

Function FTime(ByVal S As String) As String
  Dim Arr As Variant
  Arr = Split(S, ":")
  FTime = Application.Trim(IIf(Arr(0), CLng(Arr(0)) & "h ", "") & IIf(Arr(1), CLng(Arr(1)) & "m ", "") & IIf(Arr(2), CLng(Arr(2)) & "s", ""))
End Function

I will note that my code (this one and the previous one) do not check if the minutes and seconds are less than 60, but I figured that is okay because those time values are being created elsewhere in your code and I figure they are tested there for correctness.

 

[daverunt]

Cheers Rick. You mean it was 04:30 : )
A lot shorter than the code I used. Tested and works great.
Yes the time comes from a text file generated by a current measurement application and I am copying information to a different file where the format is as requested.

Many Thanks