asked on

Crystal Reprots Formula Number to time

I have a field in my database that I want to convert to time format

The field type is: Number and the data looks like this
15001595 which I want to convert to 3:00:15 pm
9360344 which I want to convert to 9:36:03 am

I do not need the milliseconds.

Thank you in advance for your help!

GJParker

Try this

//@NumberToTime
Local StringVar strTime := strReverse(cstr({Your_Field},0,''));
Local StringVar secs := strReverse(Mid(strTime,3,2));
Local StringVar mins := strReverse(Mid(strTime,5,2));
Local StringVar hrs := strReverse(Mid(strTime,7,2));

Time(hrs & ':' & mins & ':' & secs)

Open in new window

Thsi will return the time which you can then format to display in any format you wish, for teh format you have asked for just change to 12 hour

HTH

Gerhardpet

ASKER

It works when I'm using crystal 10 while editing the report but when I run the report in the ERP Crystal Run Time it does not work and I get the following error

GJParker

What version is the runtime in your ERP system ?

Gerhardpet

ASKER

Version 10

GJParker

do you have any numbers in a different format, this formula relies on your times being held as

1 or 2 digit hours
2 digit mins
2 digits secs
2 digit msecs

can you run the same report that is failing through the designer ?

Gerhardpet

ASKER

I found some of the time fields in the database are blank. How can avoid blank records?

I get this error in designer when I run the report against all records. If I only run the report against a few records it does work

I need to skip blank or invalid records

ASKER CERTIFIED SOLUTION

GJParker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gerhardpet

ASKER

That works. Thank you!