Excel - Date Month Filter

Question

I have a table that has a date column CRM Date.  I have created another column Mth Date that has is a copy of the column but has it formatted in "MMM".

Example:

1/9/2016;

22/9/2016;

3/12/2016;

Shows as:

Sep

Sep

Dec

I have created slicer and pivotable with slicer for Mth Date but when I select Sep it does not show all of the records; it seems it only picks up dates that fall on a particular date.

how can I fix this..

Answer 1

I think you just changed the cell format for "Mth Date" column as "MMM" from "Format Cells > Number > Custom". That is why while you apply the Slicer on "Mth Date" it shows a different date.

To change it properly you need to apply TEXT Function. Assume that, your "CRM Date" is in A2 cell and "Mth Date" is in B2, then use this formula in B2 cell and copy down to convert "Mth Date" as month name:

=TEXT(A2, "MMM")

Now refresh the Pivot Table, and you will see like below slicer:

Another cause would be the "DD/MM/YYYY" format you are using in current workbook. Does your PC really supports this date format? Generally it should be "MM/DD/YYYY". So please check it.

Hope it will work.

Answer 2

Glad that my reply solved your problem. Please mark it as Answered/Helpful.

Answer 3

Thanks; this has solved my issue.

Working like i expected - with thanks.

Answer 4

Hi,

I think the issue is that Mth date is only formatted as a month, but the data in it is actually still the whole date.

So instead try writing a formula in the Mth date column:

=month(A1)

where A1 is the cell from the CRM_date column

Cheers

Tom