Converting Time format to Integer 2

[qster]

Hi,
Does anyone know how to convert a time format to an integer value? I'm trying to convert HH:MM to an integer but for some reason I can't see it only converts the hours and forgets about the minutes. I'm using CR 8.0

eg.
trying to convert say:
4:45 (in HH:MM format) to 4.75 HR (Integer)

Thanks in advance

 

[mbarron]

Here's one way (I'm sure someone else has a different, and probably better way)


tonumber(left(totext({@time},"HH:mm&quot,2))+tonumber(right(totext({@time},"HH:mm&quot,2))/60

Mike

 

[qster]

That didn't work.
give me an error in the formula
Anyone else have a clue?

 

[mbarron]

What kind of field (string, number, date, time, etc) is the field you you want to convert? The formula I supplied will work if the field is a time field.

Mike

 

[qster]

The field I want to convert is right now in a string format.
Question is, if the formula works would be how to change the string field to a time field?

Quang

 

[mbarron]

The following will check for one or two digits before the ":" then change the times to the decimal.

if isnumeric(left({@time},2)) then
tonumber(left({@time},2))+tonumber(right({@time},2))/60 else
tonumber(left({@time},1))+tonumber(right({@time},2))/60
Mike

 

[qster]

should I be placing this script in the surpressed section of the format field or should I be placing it in the edit formula section.

I got a bootlean error in surpress section (x-2)
and a "formula cannot refer to itself directly or indirectly" error.

so I must be doing something wrong

 

[mbarron]

It should be it's own formula field with the {@time} being replaced by the field you want to convert to a decimal. Mike

 

[qster]

This is what I have in the formula field already for the field marked {@Time Between}

WhilePrintingRecords;
NumberVar TotalSec := {@secinbtwn};
NumberVar Hours := Truncate (Remainder (TotalSec , 86400) / 60);
NumberVar Minutes := Remainder (TotalSec , 60);

Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'')

This converts the data that I had to take total time between end_date to next start_date and converted it to HH:MM format.

when I add this to convert HH:MM to get an integer format:

if isnumeric(left({@Time Between},2)) then
tonumber(left({@Time Between},2))+tonumber(right({@Time Between},2))/60 else
tonumber(left({@Time Between},1))+tonumber(right({@Time Between},2))/60

I get this error.
i get the "formula cannot refer to itself directly or indirectly" error.

 

[kmcclung]

You need to create an entirely new formula for the conversion. You can then remove the HH:MM:SS formatted formula from the report or hide/suppress it.

 

[qster]

Thanks Mike,
Got it to work..
I create a new formula and added a supressed formula to the field format to stop the count.

Everything looks good..
except for one minor thing..
if I have a negative time, like HH:-MM it converts to a positive integer.

but I think i can solve this.

Thanks again.

 

[dgillz]

use the ABS() function to get the absolute value, no negatives. I'm not sure if that what you want. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[mbarron]

You can combine it all into one formula by declaring another variable for holding the text time.

WhilePrintingRecords;
NumberVar TotalSec := {@secinbtwn};
NumberVar Hours := Truncate (Remainder (TotalSec , 86400) / 60);
NumberVar Minutes := Remainder (TotalSec , 60);
stringvar texttime:=Totext ( Hours, '00', 0,'') + ':'+ Totext ( Minutes,'00', 0,'');


if isnumeric(left(texttime,2)) then
tonumber(left(texttime,2))+tonumber(right(texttime,2))/60 else
tonumber(left(texttime,1))+tonumber(right(texttime,2))/60 Mike

 

[qster]

Hi Mike,
It works if I use your suggestion, however it causes a problem with the formula in the surpressed (x-2) field.