Split the timestamp in to categories

[RMeghashyam]

I have few Request types. We can have have their status as Success, Failed, Expired.

I have these timestamps round the clock and I would like to filter the time between morning 10AM to 12PM in to 15 minutes intervals and group them in to categories like 10:00-10:15, 10:15-10:30. How do i do that ?

Is there any default slicer for time ? I am aware that date has but could not find time slicer. 

Example:

REQUEST	STATUS	UPDATED_TS
MoveIn	Success	14-SEP-2018 05:29
MoveIn	Success	14-SEP-2018 05:20
MoveIn	Success	14-SEP-2018 05:26
MoveIn	Success	14-SEP-2018 05:19
MoveIn	Success	14-SEP-2018 05:23
MoveIn	Success	14-SEP-2018 05:26
MoveIn	Success	14-SEP-2018 05:35
MoveIn	Success	14-SEP-2018 05:32

[zoloturu · Accepted solution]

@RMeghashyam,

Unfortunately, there is no specific slicer for time. I can propose you to create a calculated column in the same table where you have datetime column with calculation below:

TimeGroup = 
FORMAT(HOUR(Table2[Datetime column]),"00") 
& ":" 
& FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15),"00") 
& "-" 
& FORMAT(HOUR(Table2[Datetime column]),"00") 
& ":" 
& FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15+15),"00")

Where

- Table2[Datetime column] is a table and column of your datetime

Then you can add a simple slicer with this field.

Then it will look like below:

TimeGroup

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[zoloturu · Accepted solution]

@RMeghashyam,

Unfortunately, there is no specific slicer for time. I can propose you to create a calculated column in the same table where you have datetime column with calculation below:

TimeGroup = 
FORMAT(HOUR(Table2[Datetime column]),"00") 
& ":" 
& FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15),"00") 
& "-" 
& FORMAT(HOUR(Table2[Datetime column]),"00") 
& ":" 
& FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15+15),"00")

Where

- Table2[Datetime column] is a table and column of your datetime

Then you can add a simple slicer with this field.

Then it will look like below:

TimeGroup

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

[RMeghashyam]

Thanks so much for help, but how do we modify 60's. Eg: 9:60. 10:60 to 10:00, 11:00 respectively

[zoloturu]

@RMeghashyam,

10:60? What do you mean? For 10 hours maximum is 10:59. Provide more details.

Regards,
Ruslan

[RMeghashyam]

I mean as per your logic, The grouping is done properly, but for times between HH:45 to HH:60 they are getting displayed as 09:45 - 09:60

10:45-10:60

Is there a way that we can categorize them as 09-45-10:00 etc for all the time stamps. 

[zoloturu · Accepted solution]

@RMeghashyam,

I understand your point. See an updated approach:

Create 5 calculated columns:

HoursStart = Hour(Table2[Datetime column]) 

MinutesStart = ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15

HoursEnd = IF(Table2[MinutesStart]=45,Hour(Table2[Datetime column])+1,Hour(Table2[Datetime column]))

MinutesEnd = IF(Table2[MinutesStart]+15=60,0,Table2[MinutesStart]+15)

TimeGroup = FORMAT(Table2[HoursStart],"00") 
& ":" 
& FORMAT(Table2[MinutesStart],"00") 
& "-" 
& FORMAT(Table2[HoursEnd],"00") 
& ":" 
& FORMAT(Table2[MinutesEnd],"00")

Then it will behave as below:

time group 2

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!