How to calculate retirement day in Excel

There are different ways : You can write macro (VBA script).

Assumption : Retirement Date is 60 Years after Birth.


Function MYDATE2(pDate As Date)
'Update 20150310
Dim rng As Range
Dim xResult As Date

Dim xDay As Integer
Dim xMonth As Integer
Dim xYear As Integer

Dim LDate As Date

xDay = Day(pDate)
xMonth = Month(pDate)
xYear = Year(pDate)

If xDay = 1 Then xMonth = xMonth - 1

LDate = DateSerial(xYear + 60, xMonth, xDay)



MYDATE2 = LastDayInMonth(LDate)
End Function


======Second Function=======


Function LastDayInMonth(Optional pDate As Date = 0) As Date
'Updateby20140723
If pDate = 0 Then pDate = VBA.Date
LastDayInMonth = VBA.DateSerial(VBA.Year(pDate), VBA.Month(pDate) + 1, 0)
End Function

========================================================================