Number to Time Conversion 1

[kcoleman26]

CR 2008

I am trying to convert a number to a time ex(920 to 9:20AM, 1330 to 1:30PM).

Once converted I would want to round the time up or down in 30 minute increments

8:00 AM
8:30 AM
9:00 AM
using 7:45 - 8:14 = 8:00 AM
8:15 - 8:44 = 8:30 AM
8:45 - 9:14 = 9:00 AM
and so on.

Thanks,
KC

 

[jdemmi]

The follwing should convert your string into a Crystal Reports recognized time field


Create a formula

local numbervar hours1 := ToNumber(Left({@timefield}, 1));
local numbervar hours2 := ToNumber(Left({@timefield}, 2));
local numbervar mins1 := ToNumber(Mid({@timefield}, 2, 2));
local numbervar mins2 := ToNumber(Mid({@timefield}, 3, 2));
local numbervar secs := 00;


if length({@timefield})<4 then
Time(hours1, mins1, secs)
else
Time(hours2, mins2, secs)


The above assumes your string will contain no more than 4 characters. It also assumes your string does not contain seconds. The reason I have 2 hours variables and 2 minutes variables is to accomodate your string not containing a leading 0 (920 instead of 0920)

From here you should be able to right click on the fiels when placed on the report and select FORMAT FIELD. Since CR now sees it as a time you can round accordingly by clicking on the TIME tab and then then clicking the CUSTOMIZE button.

You can set SECONDS to NONE and then set your HOUR and MINUTES accordingly.

Does this make sense? Will it work for you?

-- Jason
"It's Just Ones and Zeros

 

[lbass]

Or you could use a formula like this:

//{@time}:
stringvar x := totext({table.number},"0000");
time(val(left(x,2)),val(right(x,2)),0)

To round, use a second formula like this:

if minute({@time}) < 15 then
time(hour({@time}),0,0) else
if minute({@time}) in 15 to 44 then
time(hour({@time}),30,0) else
if minute({@time}) in 45 to 59 then
dateadd("h",1,time(hour({@time}),0,0)

-LB

 

[kcoleman26]

lbass

This first formula works beautifully. Thank You

The second one is asking for another ")" I played around with putting the close parentheses in other places but I keep getting other errors. Like when I put it at the end it says a date is required

 

[lbass]

Sorry, that should be:

if minute({@time}) < 15 then
time(hour({@time}),0,0) else
if minute({@time}) in 15 to 44 then
time(hour({@time}),30,0) else
if minute({@time}) in 45 to 59 then
time(dateadd("h",1,datetime(currentdate, time(hour({@time}),0,0))))

-LB

 

[kcoleman26]

Going back to this formula it worked because I already had my data on the screen. Now when I run it is says Hour must be between 0 and 23

stringvar x := totext({table.number},"0000");
time(val(left(x,2)),val(right(x,2)),0)


I went back and looked at the data coming into Crystal and noticed when I insert that field into my report the time field that was 920 really comes accross as 920.00

This field can have 0.00 value

What can I do to keep this from happeing?

 

[jdemmi]

You need to check the length of the string and if it is less than 4 ad a preceding zero. I thought LBass's did that but I have not tested it.

Can you try mine?

-- Jason
"It's Just Ones and Zeros

 

[kcoleman26]

jdemmi

When I use the below formula I don't get any error but when I try to add the field to my report I get and error "The String is non-numeric"

local numbervar hours1 := ToNumber(Left({GIP.CIAGGI}, 1));
local numbervar hours2 := ToNumber(Left({GIP.CIAGGI}, 2));
local numbervar mins1 := ToNumber(Mid({GIP.CIAGGI}, 2, 2));
local numbervar mins2 := ToNumber(Mid({GIP.CIAGGI}, 3, 2));
local numbervar secs := 00;


if length({GIP.CIAGGI})<4 then
Time(hours1, mins1, secs)
else
Time(hours2, mins2, secs)

 

[jdemmi]

Change the < 4 to <> 4.

I was not accomodating for strings longer than 4.

-- Jason
"It's Just Ones and Zeros

 

[kcoleman26]

Still getting "The String is non-numeric" it is pointing to this part of the formula

ToNumber(Left({GIP.CIAGGI}, 1))

 

[jdemmi]

Try this on your time field...

//formula
local numbervar decpnt := instr("920.00",".");
cstr(val("920.00" [1 to decpnt]))

-- Jason
"It's Just Ones and Zeros

 

[lbass]

My formula would not have failed in that way. It sounds like you might actually have nulls, so try:

stringvar x;
if isnull({table.number}) then
x := "0000" else
x := totext({table.number},"0000");
time(val(left(x,2)),val(right(x,2)),0)

-LB

 

[kcoleman26]

Okay for some reason the database is accepting numbers like 2414.00, 2433.00, 2646.00. Which their is no such time as 2414 or 2646 so is there a way that I can tell tell the report if the number is greater than 2359 then don't print it?

 

[jdemmi]

not to step on LBass's toes...

stringvar x;
if (isnull({table.number}) or {table.number} > 2359) then
x := "0000" else
x := totext({table.number});
time(val(left(x,2)),val(right(x,2)),0)

The above would account for "times" > 2359.

You could add the sam condition,{table.number} > 2359 , to the selct expert but this would actually prevent the rows from being returned in the report output. I am not sure that's what you want to do.

-- Jason
"It's Just Ones and Zeros