satya (Executive Excise) (1839 Points)
02 November 2012
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.
O G
(PN)
(40 Points)
Replied 02 November 2012
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
satya
(Executive Excise)
(1839 Points)
Replied 03 November 2012
O G
(PN)
(40 Points)
Replied 03 November 2012
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.
@VaibhavJ
(Believe!! Live your dreams!)
(33516 Points)
Replied 05 November 2012
Hi
You can use simply EDATE function:
=EDATE(startDATE , no. of month)
in your case;
=EDATE(1-May-71 , 58*12)
satya
(Executive Excise)
(1839 Points)
Replied 05 November 2012
@VaibhavJ
(Believe!! Live your dreams!)
(33516 Points)
Replied 05 November 2012
hey rilli glad to help..
Cheerz to CCI'tiez!!
Excel Mastery Program
CAclubindia
