Convert Number Field to Time Field

[brooksl]

Any help is greatly appreciated! I'm working with Crystal Reports 11. I have a field that's formatted as a number field that I need to convert to a time field. The data in it looks like this:

172,954 (which I need to convert to time 00:17)
1,365,765 (which I need to convert to time 01:36)
10,034,058 (which I need to convert to time 10:03)
16,293,935 (which I need to convert to time 16:29)

After getting the formula straightened out, I plan to use the time in the select expert so that the user can choose a time; it will be after an "arrival_time" field and before a "closed_time" field.

 

[CoSpringsGuy]

16,293,935 (which I need to convert to time 16:29)

is that 16 minutes 29 seconds?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[brooksl]

No, it's military time for 4:29PM, but I need the format to stay military time and also need to make sure that the decimal points are truncated.

 

[brooksl]

I've done a step by step in several different formulas, from turning the numbers into text, adding several zero's in front of the text, then using the right & mid functions, pulling out the hh/mm/ss to put together in a time formula. So far that's working, however only if the field option for a number is -1123 and doesn't include decimals. Since I can't ensure that this format will work for all reports for each user, I need to find a way for it to work by getting rid of the decimals myself. If this possible? I think I need to start over from scratch, unfortunately, I'm not even sure where to begin with a new formula.

Thanks for any help or ideas.

 

[CoSpringsGuy]

To me, that seems a very strange format for time if 16,293,935 is supposed to convert to 4:29 (16:49).....

Based on that though, this should work:

//
{@time 1}/1000000

Right click the formula after placing it in your report, select Format Field. In Number and custom style change the decimal seperator to :


I'm wondering though if you might want to explore the possibility that 16,293,935 represents milliseconds?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[brooksl]

Thanks so much. That worked! I'll be completely set if I can train the users to enter criteria in the proper format. Thank you so much for your assistance.