Last Day of Month Generator

[MOlinski]

Hello,

We are trying to create a workflow for aging invoices and are a little stuck. So we are running this report in August, but would like a column that produces the last day of the month for the prior month, so in this case, we would like 2024-07-31 to repeat down the column.

Example:

Invoice Dates

2024-05-27
2023-04-15
2024-06-05
2024-07-29

Expectation:

Invoice Dates	Date
2024-05-27	2024-07-31
2023-04-15	2024-07-31
2024-06-05	2024-07-31
2024-07-29	2024-07-31

[binuacs · Accepted solution]

@MOlinski use the DateTime functions

toDate(DateTimeAdd(DateTimeFirstOfMonth(),-1,'day'))

[MOlinski]

Thank you @binuacs , also, 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

[binuacs]

@MOlinski