All of the details of handling dates (and there are a lot more details than most people realize) are handled automatically when using ADDMONTHS() and also when adding/subtracting a number of days, e.g. TODAY() - 3
You are correct in being concerned about using the results of that in DATE() though.
ADDMONTHS(DATE(2024-05-31), -1) results in 2024-04-30, so a work anniversary of 05-31 isn't going to cause an issue.
ADDMONTHS(DATE(2024-05-30), -1) would also result in 2024-04-30
If you end up with something like DATE(2025, 2, 29) or DATE(2021, 6, 31), Salesforce won't complain when you try to save the formula, but it'll either be null when you go to use it, result in some error, or perhaps both.
The only scenario you need to worry about is the end of March. If you're ok with the formula giving a result that's 1 day early on leap years (which shouldn't be that big of a stretch if you're ok with 05-30 and 05-31 both becoming 04-30), then you can simply use an IF() to handle that scenario.
IF( /* March 29th, 30th, and 31st present problems */ MONTH(Date) = 3 && DAY(Date) >= 29, /* For safety, just make the end of March result in February 28th */ DATE(YEAR(TODAY()), 2, 28), /* Otherwise, just let ADDMONTHS() work its magic */ /* As long as you use ADDMONTHS() for both the Month and Day, it'll be fine */ DATE(YEAR(TODAY()), MONTH(ADDMONTHS(Date, -1)), DAY(ADDMONTHS(Date, -1))) )
