The simplest way to do this is with the EDATE function.
Embedding calculations this way can help make the assumptions and purpose of a formula easier to understand.
Note: EDATE returns a date as aserial number, so apply a datenumber formatto display as a date.
TheDAY functionreturns the day of the birthdate.
If the day is 1, the expression returns TRUE.
Otherwise, the expression returns FALSE.
The math operation of subtraction coerces TRUE to 1 and FALSE to zero.
This moves first-of-month birthdays to the last day of the previous month.
To use it, we subtract today’s date from the retirement date.
you could see the result in row 8, where the retirement date has already passed.
Ifnumberis positive, SIGN returns 1.
Ifnumberis negative, sign returns -1.
Ifnumberis zero, SIGN returns 0.
Related videos
How to calculate and highlight expiration dates
