Converting a DateTime Field to Sum

[JenB]

Hi,

I have a date time field (where only the time part is meaningful) which I need to be able to insert as a sum into a crosstab....

I am unsure as to how to convert the time into a numeric so I can add it up and then convert back into total time spent.

Your help/advice will be gratefully received

My fist will soon be going thru the monitor if I don't work it out soon ;-)

Ta!

Jen

 

[HowardHammerman]

Don't abuse your monitor!

Create the formula below. Note the use of upper and lower case in the totext function. It is case sensitive.

whileprintingrecords;
stringvar input := totext({table.datetime},"HHmmss"
numbervar hrs := value(input[1 to 2]);
numbervar min := value(input[3 to 4]);
numbervar sec := value(input[5 to 6]);
numbervar totsec := sec + (min * 60) + (hrs * 3600);
truncate(totsec/60)


The last line will take the total number of seconds and turn it into whole minutes (rounded down).

You can play with this to get it just the way you want it.
Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[JenB]

Thanks,

I tweaked it a bit & it works... I had to take out the whileprintingrecords line, because it wouldn't work inside my crosstab, but it still calculates fine.

My monitor has survived another day!

Jen