Forum Discussion

jabel49048's avatar
jabel49048
Copper Contributor
Mar 14, 2023

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.

 

  • 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's avatar
    Detlef_Lewin
    Silver Contributor

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

     

    • jabel49048's avatar
      jabel49048
      Copper 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_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        jabel49048 

        That depends on your regional settings in Windows.

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

         

Resources