Forum Discussion
jabel49048
Mar 14, 2023Copper Contributor
Slicer Trouble
I have a table of data that spans two years. 2022 and 2023. I have Column B that is formatted with a header called Month and is formatted custom "mmmm" and it's cell contents for each row of data is "1/1/2022" and "1/1/2023" respectively. So they actually display January, February, March, etc. So I have a slicer that is for this column and it shows month names. I have another slicer for Column C that is the year so for every row that contains 2022 and 2023 data, the format is again, "yyyy" so it displays 2022 & 2023 when the data "1/1/2022 & 1/1/2023" is entered for all the records.
Now for the trouble. When I select 2022 in the Year Slicer, I can switch between months. However when I select 2023 in the Year Slicer, and go to click January or February, it shows no data. Interestingly enough, if I click on 2023, and leave both January and February selected in the Month Slicer, all the data shows. As soon as I filter the month, it messes up and data disappears.
I am attaching screen shots here for reference.
The slicers show the formatted serial date as "January" but the cell content still is 01/01/2022. And if you filter for year 2023 and don't reset the slicer for the month it will filter for 01/01/2022. And there is no match.
I would suggest a date column and for the year and month use the TEXT() function.
=TEXT(date;"YYYY") =TEXT(date;"MMMM")
- Detlef_LewinSilver Contributor
The slicers show the formatted serial date as "January" but the cell content still is 01/01/2022. And if you filter for year 2023 and don't reset the slicer for the month it will filter for 01/01/2022. And there is no match.
I would suggest a date column and for the year and month use the TEXT() function.
=TEXT(date;"YYYY") =TEXT(date;"MMMM")
- jabel49048Copper Contributor
Detlef_Lewin Ok. Are you saying to leave the formatting of the column as date with custom format of "mmmm" and "yyyy" respectively but the cell contents to be =TEXT(2/1/2023,"MMMM")? When I put this value in the cell, it shows up as January but obviously should be February.
- Detlef_LewinSilver Contributor
That depends on your regional settings in Windows.
It is probably not dd/mm/yyyy but mm/dd/yyyy.