Splicer vs Filter different results

%3CLINGO-SUB%20id%3D%22lingo-sub-2234335%22%20slang%3D%22en-US%22%3ESplicer%20vs%20Filter%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234335%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20have%20a%20client%20list%20with%20dates....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22date%201.PNG%22%20style%3D%22width%3A%20260px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266908i2B8FB7E828DF9239%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22date%201.PNG%22%20alt%3D%22Full%20list%20of%20dates%2C%20no%20filter%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EFull%20list%20of%20dates%2C%20no%20filter%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Date%202.PNG%22%20style%3D%22width%3A%20328px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266909i757ABE4CA623713D%2Fimage-dimensions%2F328x326%3Fv%3Dv2%22%20width%3D%22328%22%20height%3D%22326%22%20role%3D%22button%22%20title%3D%22Date%202.PNG%22%20alt%3D%22Copied%20column%20with%20dates%20changed%20to%20Months%20for%20filter%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECopied%20column%20with%20dates%20changed%20to%20Months%20for%20filter%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22date%203.PNG%22%20style%3D%22width%3A%20275px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266910iEA7D40D931D6521C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22date%203.PNG%22%20alt%3D%22When%20I%20filter%20with%20Splicer%2C%20single%20dates%20appear%2C%20not%20all%20of%20that%20month.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EWhen%20I%20filter%20with%20Splicer%2C%20single%20dates%20appear%2C%20not%20all%20of%20that%20month.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22date%204.PNG%22%20style%3D%22width%3A%20271px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266916i023B3288F1CEB511%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22date%204.PNG%22%20alt%3D%22date%204.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFilters%20correctly%20if%20I%20use%20the%20Filter%20in%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnsure%20why.%20Seems%20that%20the%20splicer%20actually%20only%20hunts%20for%20the%20first%20instance%20of%20the%20actual%20date%20and%20filters%20by%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20if%20I%20can%20fix%20this%20or%20am%20I%20going%20to%20have%20to%20create%20a%20formula%20column%20to%20sort%20months%20properly%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2234335%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2234407%22%20slang%3D%22en-US%22%3ERe%3A%20Splicer%20vs%20Filter%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234407%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3BThis%20seems%20to%20have%20been%20around%20since%20Slicers%20where%20introduced.%20Slicers%20on%20tables%20don't%20work%20very%20will%20with%20dates.%20The%20attached%20article%20describes%20the%20issue%20and%20the%20workarounds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.myonlinetraininghub.com%2Fsorting-excel-date-slicers%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.myonlinetraininghub.com%2Fsorting-excel-date-slicers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2236455%22%20slang%3D%22en-US%22%3ERe%3A%20Splicer%20vs%20Filter%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2236455%22%20slang%3D%22en-US%22%3EThank%20you.%20hmmm%2C%20that%20poses%20me%20some%20issues.%20custom%20lists%20isn't%20an%20option%20given%20who%20needs%20to%20access%20this%2C%20and%20I%20can't%20remove%20the%20original%20dates%20as%20they're%20the%20important%20bit%20that%20needs%20to%20be%20seen.%3CBR%20%2F%3E%3CBR%20%2F%3ECreating%20the%20second%20column%20with%20custom%20formatting%20was%20what%20I%20have%20done%20already%2C%20and%20that's%20what%20is%20not%20working%2C%20it's%20going%20to%20single%20lines%20instead%20of%20all%20feb-21s%20for%20example.%3CBR%20%2F%3E%3CBR%20%2F%3EHmmmmmm.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2236485%22%20slang%3D%22en-US%22%3ERe%3A%20Splicer%20vs%20Filter%20different%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2236485%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fget-month-name-from-date%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fget-month-name-from-date%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3ENon-ridiculous%20formula%20to%20end%20up%20with%20same%20result.%20This%20works%20for%20me%2C%20and%20will%20allow%20the%20dynamic%20data%20to%20still%20update%20fine.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Contributor

So I have a client list with dates....

 

Full list of dates, no filterFull list of dates, no filter

 

Copied column with dates changed to Months for filterCopied column with dates changed to Months for filter

 

When I filter with Splicer, single dates appear, not all of that month.When I filter with Splicer, single dates appear, not all of that month.

 

 

 

 

 

 

 

 

 

date 4.PNG

Filters correctly if I use the Filter in table.

 

Unsure why. Seems that the splicer actually only hunts for the first instance of the actual date and filters by that.

 

Any ideas if I can fix this or am I going to have to create a formula column to sort months properly?

 

 

3 Replies

@Davidm54 This seems to have been around since Slicers where introduced. Slicers on tables don't work very will with dates. The attached article describes the issue and the workarounds.

 https://www.myonlinetraininghub.com/sorting-excel-date-slicers 

Thank you. hmmm, that poses me some issues. custom lists isn't an option given who needs to access this, and I can't remove the original dates as they're the important bit that needs to be seen.

Creating the second column with custom formatting was what I have done already, and that's what is not working, it's going to single lines instead of all feb-21s for example.

Hmmmmmm.
https://exceljet.net/formula/get-month-name-from-date

Non-ridiculous formula to end up with same result. This works for me, and will allow the dynamic data to still update fine.