need to round off to the nearest 1/4 hour 1

[DOGWATCH]

I have this formula in crystal that has a time calculation. It sums up the minutes then divides by 60 to get the number of hours.

Formula=Cdbl(Sum ({@cRegular}, {usp_rptTimeSheet;1.Vehicle_Name})/60)

I need to change this code so it will round up that value to the nearest 1/4 hour so if it calculates 5:05 it get rounded to 5:15 6:50 would be rounded to 7:00 and so on. Any ideas how to change that formula to accomplish that task ???

 

[synapsevampire]

You'd have to share technical information such as the formula used for time to have someone edit it ({@cRegular}).

Consider posting technical information when posting, such as:

Crystal version
Database used
Example data
Expected output

Demonstrating part of what doesn't work isn't very useful.

Try something like the following, substituting a datetime field for currentdatetime:

if minute(currentdatetime) = 0 then
cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),
hour(currentdatetime),0,0)
else if minute(currentdatetime) < 16 then
cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),
hour(currentdatetime),15,0)
else if minute(currentdatetime) < 31 then
cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),
hour(currentdatetime),30,0)
else if minute(currentdatetime) < 46 then
cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),
hour(currentdatetime),45,0)
else if minute(currentdatetime) < 61 then
dateadd("h",1,cdatetime(year(currentdatetime),month(currentdatetime),day(currentdatetime),
hour(currentdatetime),0,0))

-k

 

[DOGWATCH]

If this helps any

Crystal version
:8

Database used
:SQL 2000

Example data
:Crystal will sum the amount of minutes over a date range
and return minutes, this is then divided by my formula to determine hours.

Expected output
Regular Time 35:10 showing a time value of
30 hours 10 minutes should be formatted to reflect
the nearest quarter hour so it should read:

35:15

We always round up.

So I need to replace this formula

Formula=Cdbl(Sum ({@cRegular}, {usp_rptTimeSheet;1.Vehicle_Name})/60)

with a formula that does the rounded calculations. Sorry I am only a beginner on Crystal Reports.

 

[DOGWATCH]

this thread got burried any takers?

 

[DOGWATCH]

sorry, never figured this one out. Any clues?

 

[lbass]

We are probably all wondering why you have not tried or responded to SV's suggestion. If it did not work, then you should explain why it did not work for you, so other respondents can build from there.

-LB

 

[DOGWATCH]

I pasted that in and it shows an error in the formula. I don
't know quite enough about crystal reports formulas to troubleshoot and for that matter crystal 8 doesn't seem to give you very descriptive error messages.

 

[lbass]

So what was the error message, and where was the cursor located? What datatype is your datetime field?

-LB

 

[DOGWATCH]

check reveals. A number, currency amount, boolean, time date or string expected here. as part of the error message.

the previous fomula was

Formula=Cdbl(Sum ({@cRegular}, {usp_rptTimeSheet;1.Vehicle_Name})/60)

and I am trying to reformat that formula to give me a value that will be rounded to the nearest 1/4 hour. My database is SQL.

@cRegular is a value of the sum of minutes, I divide that by 60 to give the number of hours.

Hope that make it a bit more clear.

 

[lbass]

This is an adaptation of an FAQ written by SynapseVampire, and using Crystal syntax, not Basic. First change your formula so that it results in seconds, not hours:

//{@yourformula}:
Cdbl(Sum ({@cRegular}, {usp_rptTimeSheet;1.Vehicle_Name})*60) //multiply by 60 instead of dividing by 60

//Use the revised formula in the following formula:
whileprintingrecords;
numberVar dur := {@your formula};
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
if min in 01 to 15 then min := 15 else
if min in 16 to 30 then min := 30 else
if min in 31 to 45 then min := 45 else
if min in 46 to 59 then
(min := 00;
hrs := hrs + 1) else
min := min;
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

-LB

 

[DOGWATCH]

OK sounds like above might work, but a little careifaction. I am dropping in the code shown below and I get an error message "A statement is expected here", do I need to add some statement like "formula= ..." its just a little unclear whats comments and what is not. It still doesn't want to check out as OK.

//{@yourformula}:
Cdbl(Sum ({@cRegular}, {usp_rptTimeSheet;1.Vehicle_Name})*60) //multiply by 60 instead of dividing by 60

//Use the revised formula in the following formula:
whileprintingrecords;
numberVar dur := {@your formula}; 
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
if min in 01 to 15 then min := 15 else
if min in 16 to 30 then min := 30 else
if min in 31 to 45 then min := 45 else
if min in 46 to 59 then
(min := 00;
hrs := hrs + 1) else
min := min;
sec := Remainder(dur,60);
hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");
hhmmss

 

[lbass]

In the formula area, change the syntax dropdown to Crystal (not Basic).

-LB

 

[DOGWATCH]

Did that but

I pasted exactly what I had shown above into the formula and it returned: the remaining text does not appear to be part of the formula.

I am sure its somehting very simple what I am going wrong here.

 

[lbass]

Those were two separate formulas. Just paste the following into a new formula, after changing your original formula as noted previously:

//Use the revised formula in the following formula:
whileprintingrecords;
numberVar dur := {@your formula};
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
if min in 01 to 15 then min := 15 else
if min in 16 to 30 then min := 30 else
if min in 31 to 45 then min := 45 else
if min in 46 to 59 then
(min := 00;
hrs := hrs + 1) else
min := min;
sec := Remainder(dur,60);
hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");
hhmmss

Or, just create one formula:

whileprintingrecords;
numberVar dur := Cdbl(Sum ({@cRegular}, {usp_rptTimeSheet;1.Vehicle_Name})*60);
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
if min in 01 to 15 then min := 15 else
if min in 16 to 30 then min := 30 else
if min in 31 to 45 then min := 45 else
if min in 46 to 59 then
(min := 00;
hrs := hrs + 1) else
min := min;
sec := Remainder(dur,60);
hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");
hhmmss

Again, you need to change the syntax to Crystal.

-LB

 

[DOGWATCH]

that was the ticket!!! thanks so much for your help.