You’re offline. This is a read only version of the page.
292
For accounting purposes, I need to calculate the last day of the month a bill is due.
There is (currently) no "Last Day of the Month" or "End of Month function in P/Au" --- so how do I calculate EOM() date?
Short answer:
addDays(startOfMonth(addDays(startOfMonth('2022-08-03'),31)),-1)
There is now a startOfMonth() function that greatly simplifies this from older solutions to this question I see out there.
To break this down from the inside out:
This approach lets Microsoft worry about “30 days hath September…” and leap years
I put a series of Due Dates into an array, did a Select using this formula to calculate Last Day Of Month for the Due Date:
Results:
Due Date | Last Day of Month |
2022-01-01 | 2022-01-31 |
2022-01-02 | 2022-01-31 |
2022-01-29 | 2022-01-31 |
2022-01-30 | 2022-01-31 |
2022-01-31 | 2022-01-31 |
2022-02-01 | 2022-02-28 |
2022-02-02 | 2022-02-28 |
2022-02-28 | 2022-02-28 |
2022-03-01 | 2022-03-31 |
2022-03-02 | 2022-03-31 |
2022-03-03 | 2022-03-31 |
2022-03-29 | 2022-03-31 |
2022-03-30 | 2022-03-31 |
2022-03-31 | 2022-03-31 |
I hope this is helpful – it was nice to have a chance to give back after this board has saved me so many times!
Share
Report
All responses (
Answers (
