Mar 14 2023 06:38 AM
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.
Mar 14 2023 07:01 AM
SolutionThe 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")
Mar 14 2023 07:09 AM
@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.
Mar 14 2023 07:22 AM
That depends on your regional settings in Windows.
It is probably not dd/mm/yyyy but mm/dd/yyyy.
Mar 14 2023 07:26 AM
@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.
Mar 14 2023 07:54 AM
I would strongly advise to use a cell reference and not putting the actual date in the formula.
Mar 14 2023 07:56 AM
Mar 14 2023 08:30 AM
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.
Mar 14 2023 08:39 AM