## Number to Time Conversion

## Number to Time Conversion

(OP)

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

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

## RE: Number to Time Conversion

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"

## RE: Number to Time Conversion

//{@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

## RE: Number to Time Conversion

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

## RE: Number to Time Conversion

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

## RE: Number to Time Conversion

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?

## RE: Number to Time Conversion

Can you try mine?

-- Jason

"It's Just Ones and Zeros"

## RE: Number to Time Conversion

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)

## RE: Number to Time Conversion

I was not accomodating for strings longer than 4.

-- Jason

"It's Just Ones and Zeros"

## RE: Number to Time Conversion

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

## RE: Number to Time Conversion

//formula

local numbervar decpnt := instr("920.00",".");

cstr(val("920.00" [1 to decpnt]))

-- Jason

"It's Just Ones and Zeros"

## RE: Number to Time Conversion

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

## RE: Number to Time Conversion

## RE: Number to Time Conversion

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"