INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

*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.

# Number to Time Conversion

 Forum Search FAQs Links MVPs

## 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

-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

-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"

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

#### 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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!