Hi everyone, I need help in creating macro that will update pivot items in my pivot table which are in date format to the latest date from the datasource. I have attached sample file as well.

problemsample.xlsx

In the sample file in sheets "pivot" i have the pivot table with concerned pivot Items in date format highlighted in different colours. The macro I should update the pivot items to latest dates according to the colour extracting data from datasource which is updated daily.

Yellow means the macro should update the date to the latest date in the datasource.
Blue, the macro should update to a date one day before yellow.
Orange, date a week before yellow.

The problem should be simple enough but I cant find a way yet to automate it in macro. It should involve manipulation of the pivot Items in the pivot table using VBA.

Appreciate it if anyone could help, Thanks.