Please help Number to time conversion 2

[mgkSHA]

I have had a very bad day with Crystal (an unrelated export issue) and I really need some help because now I am frusterated . Basically I have a time field that is stored as a number. The values range from 838.00, 1,763.00, 936.00,etc.

Basically these represent Military time within the SQL database 17:63, 9:36, etc. What I need is a formula that will convert these numbers (I know how to display them without .00 and ","'s so I have 1763, 838, etc.) into a standard AM/PM time. So basically, first convert to a valid Time, and then into a valid AM/PM time. I need this because at run-time an automated script will be comparing this time to another valid time found in a .log file, so I can not use a picture() command or simply parse out the string, I need a valid time to compare with.

Every formula I have tried has produced the same error..."An hour must be within 0 and 23"..I have used some of the examples found on this forum, with the same results. Can anyone help. I am decently fluent in Crystal, but today has been bad and I need some relief! Thanks to everyone for their help.

 

[crystalvictim]

Hi,
after playing around a little bit with CR9, I came up with the following solution, which is sure still far from being perfect (you can reduce the code to one single statement but there might also be enough potential to simplify the process):

local numbervar l_time:=1435.00;
local numbervar l_hours:=tonumber(left(totext(l_time,'0000'),2));
local numbervar l_minutes:=tonumber(right(totext(l_time,'0000'),2));
local timevar l_time2:=time(totext(l_hours,'0') & ":" & totext(l_minutes,'00'));
l_time2;

You migh replace the l_time-stuff either completely with your database-field or assign l_time the field's value.
Make sure that you change the formula field's format to the time format you want (am/pm).

I tested this solution with l_time:=1435 and l_time=840 and it seems to work.

Regards,
Frank

 

[synapsevampire]

Try:

time(int({table.timeint}/100),remainder({table.timeint},100),0)

-k

 

[whatisthis123]

I am having the same problem. I need to get minutes into a hour total that happens to above 24 hours then make ratio on total time and time taken.

Good luck.

 

[lbass]

I can't quite fathom what time 17:63 translates to. Does the military have more minutes per hour than the rest of us do?

-LB