Can I customise the new Timeline slicer in Excel 2013?

Question

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.

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?

Solution 1

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).

Solution 2

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.

Comments

[Raystafarian]

Are you open to using a helper column?

[Andi Mohr]

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

[Kyle]

You can use VBA SlicerCaches("Cache_Name").TimelineState.SetFilterDateRange "Start_Date", "End_Date"

[Andi Mohr]

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.

[Kyle]

@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.

[Andi Mohr]

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.

[AMR]

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.

[Andi Mohr]

Fair point. I guess I should be whinging about lack of competition then. Come on Google Spreadsheets, up your game!

[Andi Mohr]

Thanks but this is the same answer proposed by Kyle last year.