Convert a number (or text) to time

[roattw]

I have  a field that contains a 24 hour clock time as 4 digits.  It is a start time.  They are number fields (but could be text).  Entered simply as #### (ie 1300)

I would like to populate another hidden field with this number converted to a time value of 13:00.  Ive tried CAST and CONVERT.

 

I can do it in Excel with several different formulas but I cant find a Capsio equivalent that doesnt error.

=TEXT(field]cell,"0\:00")

Try_Convert(time(0),LEFT([@field:DTN_Start_value],2) +':'+ RIGHT([@field:DTN_Start_value],2))

Any thoughts?

 

Thanks!

Edited December 19, 2019 by roattw

[Vitalikssssss]

Hi @roattw,

You can use the following expression in Caspio:

CASE

WHEN Len([@field:YOUR_FILED]) =3 THEN Left([@field:Text],1) +':'+Right([@field:Text],2)

WHEN Len([@field:YOUR_FILED]) > 3 THEN Left([@field:Text],2) +':'+Right([@field:Text],2)

END

Hope this helps.

Regards,

vitalikssssss

[roattw]

As always, many thanks for the input!!!  Im not sure you regular responders  to questions ralize how important your help is!

On first run it had a syntax issue with the > sign.

[cheonsa]

Hi @roattw,

I tried the solution that was provided by @Vitalikssssss and it works as expected for me. Please make sure that you are using the correct field names.