Crystal Reports Time Comparisons and Averages 1

[cmsgil]

I need some help in figuring out how to create a formula that will show the difference between when an "order" was invoiced and shipped. The shipped time is taken from a field in that is mm/dd/yyyy hh:mm:ss and is formatted to show only the time. The invoiced time is the same format. When I subtract the two, my results are always 0:00 because the time ends up as 12:00AM (0:00 military). If Shipped is 12m and Invoiced is 12:45PM I need the result to be 45 min.

Then i need to add all the minutes\hours and take an average based on the number of packages shipped.

 

[synapsevampire]

How fields are formatted to display is unimportant, the version of Crystal, the database used, and the data types are important.

To get the difference between any 2 datetime fields, use the datediff function:

datediff("s",{table.shipped},{table.shipped})

This will return the seconds difference.

You can easily get the average by placing the formula in the details, right clicking and selecting Insert Summary->Average

Then you can delete the field from the details, and reference the average summary fiel in a formula (it will be in the list of report fields) to display it in DD:HH:MM:SS as referenced in my FAQ:

faq767-3543

It doen't reference days, but with a slight tweak it will.

-k

 

[cmsgil]

What does the "s" represent?

 

[cmsgil]

I suppose the "s" means seconds, so if i wanted the results to be in minutes would I put "m"?

Also I tried this and I still get 0.00 Here is what I have:
DateDiff ("s", {vwBoSci_Reports.UpdateDateTime},{@PGITime})

Where @PGITime is a formula that converts a text field to a date using this formula:
CDateTime ({vwBoSci_Reports.PGI_Time})

Not sure if that is corrett either.

 

[Madawc]

Try displaying @PGITime and {vwBoSci_Reports.UpdateDateTime}, to see if they have the values that you thing they should have. Maybe make an extra detail section below the section that the users will see on the final report. See what Crystal is doing with your work fields, which will highlight any errors or faults. I've done it and found sometimes that data was not what I expected. Other times the running total (or whatever) was at fault.

Also look up DateDiff using Help, if you have Help installed. If you don't, try getting it.

And as synapsevampire said, you need to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)

 

[cmsgil]

I have CR version 8.0.1.

I have displayed @PGITime and values display correctly as with the other field. I am using a SQL Database the field types are different. The field type of the @PGITime is a varchar while the other field is a type UpdateDateTime (datetime)

@PGITime is simple a type conversion formula in CR to convert the text value to a time.

 

[cmsgil]

The formula that ended up working was:
DateDiff ("n", CDAteTime({vwBoSci_Reports.PGI_Time}), CdateTime(Cstr(TimeValue{vwBoSci_Reports.UpdateDateTime}))))

Now I get the difference in minutes.

 

[cmsgil]

Now that I have the time difference per day per package, I am able to show the average time difference per day by creating a running total to average the value at that group level which is date. What I need now is a way to Group the report into Monday through Friday weeks and then show the average time difference per week.

Please help, I am going nuts here!!!!!

 

[Madawc]

Crystal 8.5 has DatePart, which will give you the day, week or whatever from a date.

Madawc Williams (East Anglia, UK)