[Question]: Calculating the time in just hours:minutes:seconds instead of days.hours:minutes:seconds?

[Austin]

Good Afternoon Telerik,

 

Please see the reference thread here: https://www.telerik.com/forums/telerik-averaging-time-to-a-negative-number

 

I am using Mochil's suggestion of the following.

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

However, it is showing the hours:minutes:seconds with a days. in front. How can I get the days to just be tacked on to the hours? so instead of it stopping at 24 hours and adding a day value it'll just calculate go says 48 hours for 2 days and so on. as, the day function is causing my averages to mess up greatly since it can't calculate the day function :(

 

in example:

 

Thank you!

 

[Momchil]

Hello Austin,

If we take advantage of the TimeSpan.TotalHours property, we should be able to adapt the solution from the related forum thread.

For example:

= Fields.PROVISIONALDATETIME Is Not Null ? 
  Floor((Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).TotalHours)
  + 
  (Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).ToString("\:mm\:ss")
: 
  Floor((Fields.FINALDATETIME - Fields.COMPLETIONDATETIME).TotalHours)
  + 
  (Fields.FINALDATETIME - Fields.COMPLETIONDATETIME).ToString("\:mm\:ss")

Notice that we are using the Floor math function here because the TimeSpan property is a double.

I hope this example helps you achieve the desired functionality.

Kind Regards,
Momchil
Progress Telerik

[Austin]

Momchil, YOU ARE THE MAN SIR!!!!

This worked PERFECTLY!!! If I could buy you a beer or, whatever choice beverage you prefer I would! You've helped me ton's with all my problems. Thank you!!!

 

I do have one small caveat- I am averaging the times as well. and, they are still averaging I guess the unfixed number as those haven't change. How can I get the correct averages in telerik? as if I change the code and average the above it gives an error.

Current average showing

= Avg(Fields.PROVISIONALDATETIME.TimeOfDay - Fields.COMPLETIONDATETIME.TimeOfDay).ToString("hh\:mm\:ss")

(I guess telerik isn't processing the day from the original) or, is there a better way to average the two columns so If I make a change to my fields with the times it'll update these averages similar to excel where I tell it to Average Column L & Column M?

 

I tried averaging this. But, I just get an error of "An error has occurred while processing TextBox 'textBox38':Type mismatch in expression 'Avg'."

= Avg(Fields.PROVISIONALDATETIME Is Not Null ? 
  Floor((Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).TotalHours)
  + 
  (Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).ToString("\:mm\:ss")
: 
  Floor((Fields.FINALDATETIME - Fields.COMPLETIONDATETIME).TotalHours)
  + 
  (Fields.FINALDATETIME - Fields.COMPLETIONDATETIME).ToString("\:mm\:ss"))

 

After I recalculate the average of all the time in excel I get this (which is including our new hours since excel can read it)

 

Thank you Momchil!

 

[Todor]

Hi Austin,

If I understood correctly, you would like to get the average of the Time difference disregarding the Dates.

The approach with the DateTime property TimeOfDay works correctly on our end.

The other option you may use is to remove the Date from the value, for example through the Date function:

= Fields.COMPLETIONDATETIME - Date(Fields.COMPLETIONDATETIME.Year, Fields.COMPLETIONDATETIME.Date.Month, Fields.COMPLETIONDATETIME.Date.Day)

Then you may average the remaining TimeSpan values, for example:

= Avg(Fields.COMPLETIONDATETIME - Date(Fields.COMPLETIONDATETIME.Year, Fields.COMPLETIONDATETIME.Date.Month, Fields.COMPLETIONDATETIME.Date.Day) 
-
  (Fields.PROVISIONALDATETIME  - Date(Fields.PROVISIONALDATETIME .Year, Fields.PROVISIONALDATETIME .Date.Month, Fields.PROVISIONALDATETIME .Date.Day)))

The two approaches worked identically on our end, returning correct results when the Expression was executed in a data item's Header/Footer section, so that it may access all the data and aggregate it.

Note that the Expression you used to display the Time difference in 'hh:mm:ss' actually returns a string that cannot be averaged. You may separate it into two parts. The first part will result in the string with the Total Hours, for example, '142'. The function Floor returns an 'object' and it gets converted to a string:

= Floor((Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).TotalHours)

The second part will result in a string with the format ':mm:ss', for example, ':16:00':

= (Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).ToString("\:mm\:ss")

The final result of the whole expression is from the concatenation of two strings, hence a string that cannot be averaged.

If you need to average only the Time of Day of the TimeSpan, excluding the whole days, you may consider something like:

= Avg(Fields.PROVISIONALDATETIME - AddDays(Fields.COMPLETIONDATETIME, (Fields.PROVISIONALDATETIME - Fields.COMPLETIONDATETIME).Days))

The above expression takes the difference between PROVISIONALDATETIME and COMPLETIONDATETIME, excluding the difference that represents whole days, thus leaving each averaged item to be less than 24 hours. I removed the conditional statement for clarity. The result from this expression was different from the result from the previous two expressions.

If you have a different requirement, feel free to send us sample data for local investigation.