8 Replies Latest reply on Apr 23, 2019 6:06 PM by Christine Curtis

Convert String into HH:SS:MM

Hello, I have a field that capture the document creation time.

The issue is that the format of this field is, for example, either:

93539 (5 numbers) for 09:35:39

Or

102743 ( 6 Numbers)  for 10:24:43 Seconds.

I am looking for a formula to add convert this into HH:MM:SS

I am not sure what calculated field could be created for this, any advice would be more that welcome.

thank you

Benjamin

• 1. Re: Convert String into HH:SS:MM

hI, Benjamin

Try this calculation:

IF Floor(Int("93539")/10000) <=9 THEN

"0"+STR(Floor(Int("93539")/10000))

ELSE

STR(Floor(Int("93539")/10000))

END

+":"+

LEFT(RIGHT("93539",4),2)

+":"+

RIGHT("93539",2)

and the result looks like this: Hope this helps

ZZ

2 of 2 people found this helpful
• 2. Re: Convert String into HH:SS:MM

You may try this also,

IF LEN(STR([Datetime]))=5 THEN

"0"+LEFT(STR([Datetime]),1)+":"+MID(STR([Datetime]),2,2)+":"+RIGHT(STR([Datetime]),2)

ELSE

LEFT(STR([Datetime]),2)+":"+MID(STR([Datetime]),3,2)+":"+RIGHT(STR([Datetime]),2)

END Mahfooj

• 3. Re: Convert String into HH:SS:MM

Thank you Mahfooj,

this works well.

• 4. Re: Convert String into HH:SS:MM

You're welcome

• 5. Re: Convert String into HH:SS:MM

Hello,

How would you apply the same if the String is with 3 Numbers & 4 Numbers?

Thanks

Benjamin

• 6. Re: Convert String into HH:SS:MM

Hello khan, would you be able to advise.. I am still stuck with this

• 8. Re: Convert String into HH:SS:MM

I don't pretend to fully understand why this worked for a 4 digit time code but here is the formula I used.

IF LEN(STR([Scheduled Start Time]))=3 THEN

"0"+LEFT(STR([Scheduled Start Time]),1)+":"+RIGHT(STR([Scheduled Start Time]),2)

ELSE

LEFT(STR([Scheduled Start Time]),2)+":"+RIGHT(STR([Scheduled Start Time]),2)

END