Limit range of timeline - Excel

[misen]

Hi,

Is there a way to limit the range of a timeline created in Excel?
The issue in my file is that the timeline date starts at 01 Jan 2017 and ends on 31 Dec 2018,
but my data cover only from Nov 2017 to march 2018.

Thanks.

File: https://ufile.io/sov2w

 

[Kamolga]

You can run this macro

Sub Timeline_date()
Dim sl As Slicer, si As SlicerItem, pt As PivotTable, ws As Worksheet, st$
Set ws = ActiveSheet:   st = ""
On Error Resume Next
For Each pt In ws.PivotTables
    For Each sl In pt.Slicers
            sl.SlicerCache.TimelineState.SetFilterDateRange "01/01/2017", "01/04/2018"
    Next
Next
On Error GoTo 0
End Sub

 

[misen]

You can run this macro

Sub Timeline_date()
Dim sl As Slicer, si As SlicerItem, pt As PivotTable, ws As Worksheet, st$
Set ws = ActiveSheet:   st = ""
On Error Resume Next
For Each pt In ws.PivotTables
    For Each sl In pt.Slicers
            sl.SlicerCache.TimelineState.SetFilterDateRange "01/01/2017", "01/04/2018"
    Next
Next
On Error GoTo 0
End Sub

Thanks for your suggestion. However, it only selects the date range. It doesn't the unwanted ones. Is there any way to do this ?

 

[ClaireS]

Out of date I know, but just in case somebody else is looking... I find that timeline slicers give you the whole of any year in which the dates fall - plus a couple of extra days for good measure! I was working on a sample dataset all in 2016 for a training course, with Order Dates from 2nd January to 31st December. The timeline slicer gave me the whole of 2016 (obviously) but also the whole of 2017. I tried removing December dates from the end. 30th December still gave me 2017, but 29th December gave me only 2016! I suppose you need the whole year, because one of the period options is Year, but it is annoying that it goes over into a new year if you're close enough to the end of the previous one!

I have upvoted a suggestion to add "Hide items with no data" to the Timeline slicer, but it's only got 8 votes, so I doubt if it will be actioned.

Moral: Take a break between Christmas and New Year!

 

[Kamolga]

If I had only 2 months in a period of a year or so, I would go for a slicer Nov 17, March 18, not a timeline