How to calculate hours and minutes 1

[qlan]

Hi,

This is what I have in my database,
03/31/2006 1.00
04/30/2006 5.55
05/31/2006 1.45
09/30/2006 1.15

When I do the sum, what I got is 9.15 Hrs. What can I do to get 9:55 instead? Thanks

 

[Durango122]

check this

http://technicalsupport.businessobj...iceId=&dialogID=15630706&stateId=1 0 15632610

cheers


Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[qlan]

The link shows me how to convert hours, minutes, seconds to one common time field. However, the data I have in the database does not separate hours, minutes, or seconds. It is all in one field. For example, there is a 1 hour and 30 minutes of Sick Time, under the Sick Time column in our database, I would have it entered as 1.30. In crystal report, how can I convert the .30 to .50 instead or what can I do so that my sum would sum up correctly like 9.55 NOT 9.15. Thanks so much.

 

[Madawc]

First, split hours from minutes. I'd assumed this would be split({memo.record},".")[1] and split({memo.record},".")[2]

Display the results and tweak the formulas till they are right.

Next, convert to numbers. You could edit the same formulas,

ToNumber(split({memo.record},".")[2])

. Again, get this right before attemting more.

Then divide the seconds by 0.6, that should give you fractions of a minute.

Round((ToNumber(split({memo.record},".")[2]))/0.6), 2)

That or something similar, trial-and-error is the best method in Crystal


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[qlan]

Formula: split({tblLostTime.Late},".")[1]
The error I have is "a string is required here". What is it that I am missing? Thanks so much.

 

[lbass]

You need to convert your number field to minutes. Use the following {@minutes} in place of your number field:

(truncate({table.number})*60)+remainder({table.number},truncate({table.number}))*100

Then in the group footer (assuming you have a group on employee), you can use a formula that converts the results back to a string:

numbervar tot := sum({@minutes},{table.employee});
numbervar hrs := tot/60;
numbervar mins := remainder(tot,60);
stringvar hhmm := totext(hrs,0,"") + ":"+totext(mins,0,"")

-LB