Forum Discussion
Solved
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 ...
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_Lewin I tried =TEST(1/1/2023,"mmmm") and also =TEXT(2/1/2023,"mmmm") and got the same result of "January" when expecting February. See screen-shot below.
I would strongly advise to use a cell reference and not putting the actual date in the formula.
- Could you please give me an example. I'm not fully understanding what you are saying. Right now, I have a temporary solution by using a custom format of "mmm-yy" and it seems to be working just fine. But I would like to go back to using the full month names.
See attached file.
You have to change the format code in the formulas.
My system is de-DE. Therefore I use JJJJ instead of YYYY.
- Thank you so very much! This will solve so much of a headache for me!