How to convert the hh:mm:ss format into groups of time period like (5, 10, 30 minutes)?

[Anonymous]

I am facing an issue of converting the date given in hh:mm:ss format (timestamps) into group of different intervals.

The time data is in the given format.

3:12:11 PM value=46

3:12:12 PM value=47

3:12:13 PM value=48

.

.

3:17:00 PM value=55

I want to group the given time instances into 5, 10, 30 minutes periods and find the average of the corresponding time period.

output

3:12:00 to 3:17:00 value= average of all the values within that time period.

[Thim · Accepted solution]

This should do the trick.

Time Grouped = TIME(HOUR(DateTimeTable[Time]);MINUTE(MROUND(DateTimeTable[Time]+1/24/60*3;1/24/60*5));00)

This part "1/24/60*5" is what decides the interval. in this case as it is *5 it is set for a 5 minute interval. change this number, if you want a different interval, or just make a column for each interval you want.

This part "1/24/60*3" is to make sure the original time gets in the right group. Just play around with it a bit, and see what fits you best. 🙂

Hope this will help.