6

Excel 2013 introduces timeline slicers for the first time. These allow you to filter pivot tables easily by selecting time ranges - either by year, quarter, month or day.

enter image description here

It's nearly a useful tool, but in the world of business (Excel's primary market!) we generally work in fiscal periods - April to March is common here in the UK.

Out of the box, this timeline slicer doesn't appear to support fiscal years - it's calendar periods only, which means it's useless for my users. If I presented them with a slicer referring to Q1 as Jan to March, they would be terribly confused. This is highly unintuitive to folk who have spent their entire careers referring to Q1 as Apr-Jun.

I can't find any way to set custom date periods, but nothing I've read online gives me a definitive answer as to whether there's any kind of solution, or not.

Can anybody either a) show me how to do this, or b) provide an authoritative source stating timelines simply don't allow custom time periods?

Improve this question
5
  • Are you open to using a helper column?
    – Raystafarian
    Jul 17, 2015 at 13:50
  • As long as we end up using the timeline slicer in ways that make sense to business users (ie fiscal periods not calendar periods, years starting in April (or other non-Jan month)) then every option is on the table! As many helper columns as you need, VBA, etc all OK
    – Andi Mohr
    Jul 17, 2015 at 15:48
  • You can use VBA SlicerCaches("Cache_Name").TimelineState.SetFilterDateRange "Start_Date", "End_Date"
    – Kyle
    Jul 17, 2015 at 17:27
  • Thanks Kyle, however this only selects a period, much the same as if I click a period in the slicer. It doesn't change the periods visible on the timeline slicer so that (eg) Q1 begins in April.
    – Andi Mohr
    Jul 20, 2015 at 8:48
  • 1
    @Andi I made that comment minutes before leaving on Friday and it seems I didn't provide enough detail into my thought process. Please see my answer below for more detail and I hope it helps.
    – Kyle
    Jul 20, 2015 at 19:22

3 Answers 3

Reset to default
3

Think I've managed to find my answer - sadly this is not possible.

This MSDN blog post by Excel MVP Zack Barresse says:

Using timelines is as easy as point and click. When looking at the control you will see the filtered range colored, with each end containing a vertical ellipses, which you can click and drag to where you want (as seen below). In addition you can click any time segment seen in the control.

Your filtered data range will be shown in the Selection Label area. Too granular or course? Change the Time Level by clicking on it, where you get a drop down to choose from years, quarters, months and days. These are not customizable.

Baffles me a bit as to why Microsoft would release this function knowing that 35% of its core users in the US don't use the calendar year for their fiscal year, including Microsoft! That figure will be even higher in other parts of the world. From wiki:

the fiscal year is identical to the calendar year for about 65% of publicly traded companies in the United States and for a majority of large corporations in the UK and elsewhere (with notable exceptions Australia, New Zealand and Japan).

Improve this answer
2
  • 1
    Microsoft has never needed or even likely intended their products to work perfectly. Once they blew Lotus and WordPerfect out of the water all they needed was an install base, and they got that by being incrementally the best. And having enough bugs in the code means more pressure on institutional buyers when a new version is released. IT departments hate to release new software and would be perfectly happy having users that were fine with Office 2000.
    – AMR
    Jul 23, 2015 at 22:55
  • Fair point. I guess I should be whinging about lack of competition then. Come on Google Spreadsheets, up your game!
    – Andi Mohr
    Jul 24, 2015 at 8:16
1

While it is true that the period available on the period select cannot be changed, you can create your own custom quarters with four buttons paired with macros. First create four macros, one for each quarter, that look like this:

Sub Quarter1()

ActiveWorkbook.SlicerCaches("NativeTimeline_Created_Date").TimelineState. _
        SetFilterDateRange "04/01/2015", "06/30/2015"

End Sub

And create four buttons, one for each quarter, that runs each macro when clicked. You then must instruct your users to click the buttons, instead of using the build in quarter selector.

Improve this answer
1
  • Thanks for the answer Kyle. It doesn't directly answer the question in that I'm specifically trying to use the Timeline slicer interface. Otherwise I could simply use regular slicers, with FY/Qtr/Month columns. Now, your approach is helpful in that it adds a lot of flexibility over regular slicers. It allows you to get very specific about what dates you're selecting, and introduce scrollbar/spin controls etc. So it is a good answer (hence +1). However, my question is seeking to take advantage of the ease of use of timeline slicers - I don't want to spend time crafting a custom UI.
    – Andi Mohr
    Jul 21, 2015 at 10:35
-1

You can use this kind of grammar:

ActiveWorkbook.SlicerCaches("NativeTimeline_Timeline1").TimelineState. _
    SetFilterDateRange year(startDate)&"/"&month(startdate)&"/"day(startdate), year(endDate)&"/"&month(enddate)&"/"day(enddate)
Improve this answer
1
  • Thanks but this is the same answer proposed by Kyle last year.
    – Andi Mohr
    Mar 17, 2016 at 10:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .