Slicer Trouble

[jabel49048]

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.

[#2 · Accepted solution]

@jabel49048 

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

[#3]

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

[#4]

@jabel49048 

That depends on your regional settings in Windows.

It is probably not dd/mm/yyyy but mm/dd/yyyy.

[#5]

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

[#6]

@jabel49048 

I would strongly advise to use a cell reference and not putting the actual date in the formula.

[#7]

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.

[#8]

@jabel49048 

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.

[#9]

Thank you so very much! This will solve so much of a headache for me!