We provide annualized services. Each service period (ISPP) begins on the 1st of a given month and ends one year later on the last day of the prior month.
Examples:
Client A could have an ISPP of May 1, 2025 - April 30, 2026.
Client B could have an ISPP of Nov 1, 2025 - October 31, 2026.
(The ISPPs are always from the 1st of a month to the last day of the prior month 1 year later.)
We are informed of the ISPP end date.
I have a formula that tracks which month of the ISPP we are in.
For example, for Client A, if today were August 17th, 2025 we would be in month 4 of that client A's ISPP.
I need to return the last day of the month, i.e. August 31st, 2025.
Using the DATEDIFF function almost works, but when I use this the months are subtracted in 30 day increments. I need to subtract 31 days for 31 day months, 30 days for 30 day months, 28 days for Feb except 29 days on leap years.
How do I get the last day of the month?