Retirement date - formula

 

Dear Sir,

 

I want retirement date formula, I am using the below formula.

formula

=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))

Actually we need when the result date is 1st, the month of retirement should be last day of before month.

If retirement day is 2nd or more , the date of retirement should be end of the current month.

Example

1:

1-May-71

   58   

     1-May-2029

 

    Requirement is 31-Apr-2029.

 

2: 10-May-71      58     10-May-2029

   

Requirement is 31-May-2029.

 

Replies (8)

for MS Excel 2007 and higher

Try this formula

=EOMONTH(IF(DAY(C1)=1,DATE(YEAR(C1),MONTH(C1)-1,DAY(C1)),DATE(YEAR(C1),MONTH(C1),DAY(C1))),0)

see attached file 

 

Thank u very much sir....

Hi, OG sir,

 

if possible try to do like this,

DOB 01-Mar-1970 means +58 years direct get result  what  we need when the result date is 1st, the month of retirement should be last day of before month. If retirement day is 2nd or more , the date of retirement should be end of the current month. 

Thanks in advance.
 

You mean doing it directly, in this case formula become too long and hard to understand.

One suggestion you can hide column C.

Pls see attached file.

T H A N K     U     V E R Y    M U C H   S I  R

Hi

You can use simply EDATE function:

=EDATE(startDATE , no. of month)

in your case;

=EDATE(1-May-71 , 58*12)

 

Thanks//Vaibhav
Click thank button to convey your appreciation. 
 

Thanks Vibhav,

simple command!! giving me answer. 

CACLUBINDIA is great yar!!!

 

hey rilli glad to help..

Cheerz to CCI'tiez!!

 

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
Featured ARTICLESHIP 17 November 2026
Associate/ Article Trainee

M/s. A V C & Co.

New Delhi

CA Inter

View Details
Company
13 November 2026
Accountant

New Mobile India

Muzaffarpur

B.Com

View Details
Company
ARTICLESHIP 14 November 2026
Article Assistant

Jitendra Agarwal & Associates

Bengaluru

CA Inter

View Details
Company
Featured ARTICLESHIP 28 October 2026
CA- Inter and Articles

N N V Satish & Co.,

Hyderabad

CA Inter

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

Aco Perfumes & Cosmetic Pvt Ltd

Mumbai

CA

View Details
Company
ARTICLESHIP 16 November 2026
Audit Executives

Loonker Godawat And Company

Bengaluru

B.Com

View Details
Company
Featured ARTICLESHIP 31 October 2026
CA Articleship

apricus india

Navi Mumbai

CA Inter

View Details
Company
Featured 07 November 2026
Paid Assisstant

DPV & Associates

Chennai

CA Inter

View Details