SOLVED

Slicer Trouble

Copper Contributor

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.

 

Screenshot 2023-03-14 at 9.32.20 AM.pngScreenshot 2023-03-14 at 9.32.00 AM.pngScreenshot 2023-03-14 at 9.31.33 AM.pngScreenshot 2023-03-14 at 9.31.24 AM.pngScreenshot 2023-03-14 at 9.33.38 AM.pngScreenshot 2023-03-14 at 9.33.51 AM.pngScreenshot 2023-03-14 at 9.34.02 AM.pngScreenshot 2023-03-14 at 9.34.45 AM.pngScreenshot 2023-03-14 at 9.34.18 AM.png

8 Replies
best response confirmed by jabel49048 (Copper Contributor)
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")

 

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

@jabel49048 

That depends on your regional settings in Windows.

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

 

@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.2023-03-14 10_25_08-Dashboard Reports by Kim and Justin.xlsx - Excel.png

@jabel49048 

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.

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

 

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

Accepted Solutions
best response confirmed by jabel49048 (Copper Contributor)
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")

 

View solution in original post