How to calculate desired retirement date

Dear Expertz,

Please give suitable formula to get my desired retirement date;

suppose person's DOB is 01-May-1971, then his retirement age is 58 years, his retirement date is 1-May-1971, actually in our company when his superannuation comes on 01 date, his superannuation date becomes before date i.e., 30-Apr-1971. if his superannuation is on 02-May-1971 means it should be extended to end of the year.

actually I am using DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)) formula to get his superannuation date, then I am using EOMONTH(IF(DAY(C1)=1,DATE(YEAR(C1),MONTH(C1)-1,DAY(C1)),DATE(YEAR(C1),MONTH(C1),DAY(C1))),0) this formula to less one day when his superannuation comes on 01st of the month.

Now you are all requested to give suitable formula to get like this;

person's DOB is 01-May-1971 superannuation comes on 01 date, his superannuation date becomes before date i.e., 30-Apr-2029. if his superannuation is on 02-May-2029 means it should be extended to end of the year. i.e., 31-Dec-2029.

Replies (12)

hi

 

I am not getting your last condition!!

 

Please explain, why it should be extended to December 31st?

 

Thanks

Hi, Vibhav,

Thanks for replying me....

that is the special condition in our company only.

Pl give suitable formula.

hi

 

so yo mean if employee DOB between Jan to April then superannuation date should be 1 days before.

 

If his DOB is after 1 may then it should be set to 31 Dec like that?

From what i understood.. try this::

 

=IF(DAY(A1)=1,EDATE(A1,58*12)-1,DATE(YEAR(A1)+58,12,31))

 

where cell A1 contauns your Date of Birth.

 

Cheers!!

Hi, Vibhav,

Thanks for response...

not like that sir, if any employee DOB is 1st date of any of month, then his superannuation is jumps to previous month last day. otherwise means from 2nd onwards the said S.A to be extended to 31-Dec-Year.

pl do the needful.

Hi vibhav ji, earlier you have given single line command.........

/forum/retirement-date-formula-226231.asp#.UhIb_dJgf74

pl do the needful.

Originally posted by : sathya

Hi, Vibhav,

Thanks for response...

not like that sir, if any employee DOB is 1st date of any of month, then his superannuation is jumps to previous month last day. otherwise means from 2nd onwards the said S.A to be extended to 31-Dec-Year.

pl do the needful.

Try this,

=IF(DAY(A1)=1,EDATE(A1,58*12)-1,DATE(YEAR(A1)+58,12,31))   where cell A1 contains your Date of Birth.  

 

Download File

Cheers!!

THANK YOU VERY MUCH SIR JI.

T H A N K... U... 

 

Hi, Vibhav ,

small correction required , i.e, when his DOB on 01-Jan-Year means his S.A date should be 1 day before, other all dates & months S.A  should be moved to 31-dec-Year.

I have noticed one error;

if DOB 01-Oct-1956, it is giving 30-09-2014, actually it should be come 31-Dec-2014 as per our requirement.

pl do the needful.

Hi

 

Check this: =IF(AND(DAY(A1)=1,MONTH(A1)=1),EDATE(A1,58*12)-1,DATE(YEAR(A1)+58,12,31))

 

Where A1 contains DOB.

 

Thanks!!

Hi, Vibhav ,

THANKS A LOT

Hi Sathya,

 

See attached file for another approch without using edate formula, i.e. No need to install Analiysis Toolpack.

 

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
Featured ARTICLESHIP 31 October 2026
CA Articleship

apricus india

Navi Mumbai

CA Inter

View Details
Company
19 November 2026
Accounts & Taxation Executive

MCSR and Associates LLP

Hyderabad

CA Inter

View Details
Company
Featured 29 October 2026
Accounts Executive

Synergy Keystone

Chennai

B.Com

View Details
Company
ARTICLESHIP 18 November 2026
Articled Assistant

A.Singhi & Co

Kolkata

CA Inter

View Details
Company
20 November 2026
Article

S & N CO

Mumbai

CA Inter

View Details
Company
02 November 2026
Audit Executive

SRJP & Co LLP

Kochi

CA Inter

View Details
Company
21 November 2026
Asst. Manager / Deputy Manager

Aco Perfumes & Cosmetic Pvt Ltd

Mumbai

CA

View Details
Company
Featured 07 November 2026
Paid Assisstant

DPV & Associates

Chennai

CA Inter

View Details