AddMonths function

[byrnel0586]

Hi,

I am needing to have 4 text objects showing the most recent 4 reporting dates. Our reporting dates are the last day of each month, so for example I need them to show 5/31/16, 4/30/16, 3/31/16, & 2/29/16. I am able to get the most recent reporting date by using the below formula, but when I try to use the AddMonths function to get the previous month, it is showing the same day value. So, it's showing 5/31/16 & 4/31/16 when I need it to be 4/30. Anyone know how to get the last day of each month to populate?

=If(Only(Year),Date(Max(ReportDate)), 'M/D/YYYY')

=If(Only(Year), Date(AddMonths(Max(ReportDate), -1), 'M/D/YYYY'))

[tamilarasu]

May be this.?

=If(Only(Year), Date(MonthEnd(AddMonths(Max(ReportDate), -1)), 'M/D/YYYY'))

[byrnel0586]

Thank you, that did work for what I had asked. However, I forgot to mention that I need the year to be the year prior... is that possible? Instead of 4/30/16 I need 4/30/15.

[trdandamudi]

May be as attached:

[tamilarasu]

Linda

You can try below expression.

=If(Only(Year), Date(MonthEnd(AddYears(Max(ReportDate), -1)), 'M/D/YYYY'))

[byrnel0586]

Thank you, I did try that but I will still need the previous month's date as well. So when the year 2017 is selected I will need the max date with the previous year (4/30/16), but also need the date of the month prior (3/31/16).

[tamilarasu · Accepted solution]

Here it is,

=If(Only(Year), Date(MonthEnd(AddMonths(Max(ReportDate), -13)), 'M/D/YYYY'))

[byrnel0586]

Thank you very much!

[tamilarasu]

My pleasure. Have a nice day.