Time range slicer

[mental1896]

I have a large table with separate date and time columns. The data is typically viewed as a pivot chart. I would like to know if there's a timeline-type filter/slicer for time only so specific time ranges can be viewed. The separate time colum exists in order to make it easier to see timing patterns independent of the date.

This is using Excel 2010

 

[oldbrewer]

you can use offset match to set start and end points to your time data and chart it

so you can choose different start time and chart will refresh

 

[mental1896]

This sounds interesting but I'm having trouble imagining how to go about doing it. Thanks for the idea.

What I've come up with in the meantime is to add a column with "=[time]*10000" as the formula and formatted it as number. Then I can go to filter -> "Number Filters" -> "Between..." and enter my min/max variables. So for this workaround, midnight is 0, noon is 5000, 23:59:59 is 9999.88, etc.

 

[oldbrewer]

'=if(b18>$h$13,na(),offset($a$1,1,match($h$12,$b$1:$n$1,0)-1+b18))

	00:00	00:15	00:30	00:45	01:00	01:15	01:30	01:45	02:00	02:15	02:30	02:45	03:00
	4	5	6	7	8	6	7	8	9	9	10	11	12
						start time	00:30
						data values	5
	1	2	3	4	5	6	7	8	9	10	11	12	13
	6	7	8	6	7	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
	formula giving first data value (6)
	=IF(B18>$H$13,NA(),OFFSET($A$1,1,MATCH($H$12,$B$1:$N$1,0)-1+B18))

<colgroup><col span="6"><col><col span="10"></colgroup><tbody>
</tbody>

 

[mental1896]

Ok. I pasted this to a spreadsheet and monkeyed around for a bit. I can see the advantage this brings if looking at only the time metric (I'm assuming that the chart would be a standard non-pivot type). However, the pivot chart is most versatile and I'd prefer to stick to it as time is only one perspective that requires observation.


Here's some clarification of my original intent: Something like the image below, but for time instead of date. If nothing exists, the easiest strategy I can see is that which I mentioned in my first reply.

Thanks again.