Invoice Aging Date Range

[MOlinski]

Hello, was wondering if you had an idea for this last column. So we currently have a column that looks at the invoice date and spits out the year, but we would like to have a general comment for anything that is 6 years or older. So for example, since we are running this in 2024, the comment would be 2018 and Prior, but when we run this in 2025, we would like it to say 2019 and Prior.

Invoice Date	Year	Aging
2024-05-27	2024	2024
2023-04-15	2023	2023
2024-06-05	2024	2024
2024-07-29	2024	2024
2016-05-01	2016	2018 and Prior
2015-05-01	2015	2018 and Prior

[griffinwelsh · Accepted solution]

@MOlinski use this formula to generate your aging column. Please mark the thread solved if this works for you.

if datetimediff(datetimenow(),[Invoice Date],"years") >= 6 then datetimeformat(datetimeadd(datetimenow(),-6,"years"),"%Y and Prior") else datetimeformat([Invoice Date],"%Y") endif

[Qiu · Accepted solution]

@MOlinski 
Similar with @griffinwelsh flow with a quick sample.