number fields to time?

[progmann]

I have three separate number field that I have to add and provide a sum for,giving me three totals.
The fields are all Number type,called: Hours, Minutes, Seconds.
I have to use these three totals and convert them to a readable time value, ex: 13:20:59 ...
Did somebody do this before? HELP!!!

 

[dcm]

Progmann: I'm sure you know about the Time() function in Crystal to convert your Hours, Minutes, Seconds into a Time field so I assume that you are looking for a means of correcting the totalled valuse of the seconds and minutes where they exceed 60. Try the following:

Numbervar totsecs:=sum(Seconds);
Numbervar totmins:=Sum(Minutes);
Numbervar tothours:=Sum(Hours);

If Remainder(totsecs,60) then totmins:=totmins+Truncate(totsecs/60) else totmins:=totmins;
If Remainder(totsecs,60) then totsecs:=Remainder(totsecs,60) else totsecs:=totsecs;
If Remainder(totmins,60) then tothours:=tothours+Truncate(totmins/60) else tothours:=tothours;
If Remainder(totmins,60) then totmins:=Remainder(totmins,60) else totmins:=totmins;
Time(tothours,totmins,totsecs)

Hope this helps

David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[kenhamady]

Progmann,

I take a little different approach than David because when you use the TIME() function you are limited to 24 hours. TIME() gives you the clock time of day, rather than a total of time spent or elapsed. So instead I use a formatted string approach. My formula is in the FAQ area, FAQ 1.b, and lists several other commonly requested formulas.

To use my approach you use the following expression on the second line of the formula in place of the dummy field:

Sum({Hours}) * 3600 + Sum({Minutes})*60 + Sum({Seconds})

This converts your three totals into one Total Seconds value.
Let me know if you need more help.

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[progmann]

While I was waiting for help, I continued my reaserch for an answer...
What I found is that the Crystal Reports that is included in VB6 is Version 4.5...wich, I am told from the Seagate faq section, does not support the TIME() function...

I am now even more confused on how to approach my problem...

Au Secours, MayDay, S.O.S, Help!

 

[kenhamady]

Version 4.6 is a pretty limited version in terms of formulas and many other features. I recommend that you upgrade, but for fun I have written a version of my FAQ formula that will work with 4.6 (I tested it in 4.5).

You might want to create a separate formula using the line in my previous post to calculate total seconds, and then use that field in the second line of this formula:

WhilePrintingRecords;
NumberVar TotalSec := {Place your total seconds here};

NumberVar Hours := Truncate (TotalSec / 3600 );
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

(if hours < 10 then '0' else '') +
Totext ( Hours,0 ) + ':'+
(if minutes < 10 then '0' else '') +
Totext ( Minutes,0) + ':'+
(if seconds < 10 then '0' else '' )+
Totext ( Seconds,0) Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[dcm]

progmann: Ken's approach is fine and will give you a display of total number of seconds if that is what you were seeking. As an alternative, you could use the timelapsed UDF which I wrote some time ago and which you can download from the European Crystal Reports User Group site at

http://www.dart-technology.com.

This works by taking 2 dates, or 2 times or 2 datetimes and expressing the time elapsed between them either with appropriate text e.g. 'hours', 'mins' etc. or without David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner