INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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
 

RE: Number to Time Conversion

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"

RE: Number to Time Conversion

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

RE: Number to Time Conversion

(OP)
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

RE: Number to Time Conversion

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
 

RE: Number to Time Conversion

(OP)
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?

RE: Number to Time Conversion

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"

RE: Number to Time Conversion

(OP)
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)

RE: Number to Time Conversion

Change the < 4 to <> 4.

I was not accomodating for strings longer than 4.

-- Jason
"It's Just Ones and Zeros"

RE: Number to Time Conversion

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

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

 

RE: Number to Time Conversion

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"

RE: Number to Time Conversion

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
 

RE: Number to Time Conversion

(OP)
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?

RE: Number to Time Conversion

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"

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close