# Telerik Averaging time to a negative number?

DataSource SQL Report Designer (standalone) Report Viewer - HTML5
Top achievements
Rank 2
Iron
Iron
Iron
Austin asked on 11 Aug 2023, 03:35 AM

Good Evening Telerik,

I ran the same report out of Crystal and Telerik. Crystal shows the correct average time. and, Telerik (sometimes?) shows the correct average time. it randomly marks some of them a negative number. (see below) The SQL query is the same between Telerik and Crystal. so maybe I am formatting something wrong in Telerik?

Here is an example of the same exam ran out of both applications. diff results on the average Comp to Prov and Comp to Final

 EXAM ID ORDERED T/D ARRIVED T/D COMPLETED T/D DICTATED T/D PROVISIONAL T/D FINALIZED T/D Comp To Prov Comp To Final Reporting Application A903912J3 7/1/2023 21:22 7/1/2023 21:58 7/1/2023 22:23 7/2/2023 0:50 7/2/2023 0:50 7/2/2023 0:50 0:02:27 0:02:27 CrystalReporting A903912J3 7/1/2023 21:22 7/1/2023 21:58 7/1/2023 22:23 7/2/2023 0:50 7/2/2023 0:50 7/2/2023 0:50 -21:33:00 -21:33:00 Telerik Reporting

## 1 Answer, 1 is accepted

1
Accepted
Momchil
Telerik team
answered on 15 Aug 2023, 10:38 AM

Hello Austin,

I am assuming that the time difference is calculated using an approach similar to the one in the following forum thread.

Copying average of two fields to a new field if field is NULL in Reporting | Telerik Forums

Is that correct?

If it is, it would explain the negative numbers because the expression takes into account only the time of day and relies on the fact that the completed, provisional, and final times occur on the same day.

We can remove the time of day and directly get the difference between the dates as follows.

`= Fields.PROVISIONALDATETIME Is Not Null ? Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME : Fields.FINALDATETIME - Fields.COMPLETIONDATETIME`

However, this will return results in one of the following formats.

`{days}.{hours}:{minutes}:{seconds} // If the time difference is greater than 24 hours`
`{hours}:{minutes}:{seconds} // Otherwise`

Would that work for you or are you not allowed to show days in the time difference?

If not, perhaps passing a custom format string to the ToString method of the calculated TimeSpan will work. For example:

`Fields.PROVISIONALDATETIME Is Not Null ? (Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).ToString("hh\:mm\:ss") : (Fields.FINALDATETIME - Fields.COMPLETIONDATETIME).ToString("hh\:mm\:ss")`

I hope this helps.

Best Regards,
Momchil
Progress Telerik

Stay tuned by visiting our roadmap and feedback portal pages, enjoy a smooth take-off with our Getting Started resources, or visit the free self-paced technical training at https://learn.telerik.com/.
Austin
Top achievements
Rank 2
Iron
Iron
Iron
commented on 08 Sep 2023, 03:36 PM

That worked perfect, Momchil!

Thank you so much!!!

Austin
Top achievements
Rank 2
Iron
Iron
Iron
commented on 11 Sep 2023, 05:45 PM | edited

Hi Momchil,

I have a new issue on how the time is displayed. How can I convert the Day.Period into just hours? If I use the text string you gave me it just removes the day portion but hours remain the same. Is it possible for instead of it displaying it as 5.12:22:01 to just tack on the days as extra hours? in example: 132:22:01?

Edit: made a new thread here to help others if someone has this question too - https://www.telerik.com/forums/question-calculating-the-time-in-just-hours-minutes-seconds-instead-of-days-hours-minutes-seconds

Thank you!

Tags
DataSource SQL Report Designer (standalone) Report Viewer - HTML5