Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
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 now!
  • 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.

kcoleman26 (TechnicalUser) (OP)
9 Sep 08 17:16
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 (MIS)
9 Sep 08 21:19
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"

Helpful Member!  lbass (TechnicalUser)
10 Sep 08 12:43
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 (TechnicalUser) (OP)
10 Sep 08 12:59
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 (TechnicalUser)
10 Sep 08 13:01
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 (TechnicalUser) (OP)
10 Sep 08 14:09
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 (MIS)
10 Sep 08 14:11
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 (TechnicalUser) (OP)
10 Sep 08 14:17
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 (MIS)
10 Sep 08 14:31
Change the < 4 to <> 4.

I was not accomodating for strings longer than 4.

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

kcoleman26 (TechnicalUser) (OP)
10 Sep 08 14:49
Still getting "The String is non-numeric" it is pointing to this part of the formula

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

 
jdemmi (MIS)
10 Sep 08 15:02
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 (TechnicalUser)
10 Sep 08 15:52
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 (TechnicalUser) (OP)
10 Sep 08 17:17
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 (MIS)
10 Sep 08 17:37
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