Time range slicer

mental1896

New Member
Joined
Sep 17, 2014
Messages
6
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
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

New Member
Joined
Sep 17, 2014
Messages
6
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,004
'=if(b18>$h$13,na(),offset($a$1,1,match($h$12,$b$1:$n$1,0)-1+b18))

00:0000:1500:3000:4501:0001:1501:3001:4502:0002:1502:3002:4503:00
4567867899101112
start time00:30
data values5
12345678910111213
67867#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

New Member
Joined
Sep 17, 2014
Messages
6
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.
J3qr6Tr.png


Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,729
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top