One Month before xlDateLastMonth

[trizzo]

Hello!

In the Excel Pivot Table I am able to set the DATE to lastmonth with

 xlDateLastMonth

, what if I want xlDateLastMonth - 1 (which means one month before xlDateLastMonth)?

Here is the code which is already working fine. I tried to use xlDateLastMonth - 1 but no success:

 pt.PivotFields("DATE").ClearAllFilters
    pt.PivotFields("DATE").PivotFilters.Add2 _
        Type:=xlDateLastMonth

The DATE on the pivot table is in this format: 5/1/2020 (m/d/yyyy). So, if I want one month before it should be 4/1/2020 (April)

Thanks!!

[6StringJazzer]

xlDateLastMonth is not the number of the last month. It is a fixed constant that tells Excel to filter the pivot table by whatever was the last month. So you can't do arithmetic on it. You need to calculate the month and use xlDateBetween.

pt.PivotFields("DATE").PivotFilters. _
   Add Type:=xlDateBetween, Value1:=DateSerial(Year(Date),Month(Date)-2,1), Value2:=DateSerial(Year(Date),Month(Date)-1,1)-1

I have not tested this. Feel free to attach your file for further help if this does not work.

[trizzo]

This is just great! Thanks a lot!!!

[6StringJazzer]

You're welcome and thanks for the rep!