SOLVED

Sort pivot table by dynamic date range

Copper Contributor

I have two pivot tables that display assessment/training dates in my workplace. I need the table on the left (See image) to display anything that is a "NO" for assessed, and older than 3 months, and the table on the right to show anything that is a "YES" for assessed and older than 2 years. I've been playing around with date filters in pivot tables and can't find a way to do this based on todays date without manually changing it every time

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@Jeremy1565 

You might add a calculated column to the source data, with formula

 

=DATEDIF(date_cell, TODAY(), "m")

 

Make sure that this new column is included in the data source of the pivot table.

You can then filter this field for >=3 in the pivot table on the left, and for >=24 in the pivot table on the right.

@Hans Vogelaar Thanks heaps, this solved the issue

1 best response

Accepted Solutions
best response confirmed by Sergei Baklan (MVP)
Solution

@Jeremy1565 

You might add a calculated column to the source data, with formula

 

=DATEDIF(date_cell, TODAY(), "m")

 

Make sure that this new column is included in the data source of the pivot table.

You can then filter this field for >=3 in the pivot table on the left, and for >=24 in the pivot table on the right.

View solution in original post