To prepare an employee list/payroll, I am putting the date of birth of a person/employee in one cell. I want MS Excel to give me the date of retirement of the employee in the next cell. (The date of retirement is the last date of the month in which he attains 55 years of age. What formula should I use?
A B C D E F
Sno Name DOB Month Day Retirement
---------------------------------------------------------------------
1 Ganen 20/03/1981 3 31 31/3/2036
2 Amit 04/05/1983 5 31 31/5/2038
3 Rajesh 05/05/1984 5 31 31/5/2039
4 Umesh 04/08/1974 8 31 31/8/2029
5 Suresh 03/03/1988 3 31 31/3/2043
This is my small solution, it might help you
Formulas to write at:
D2: =MONTH(C2)
E2: =IF(OR(D2=1,D2=3,D2=5,D2=7,D2=8,D2=10,D2=12),31,30)
F2: =CONCATENATE(E2,"/",MONTH(C2),"/",(YEAR(C2)+55))
Some problem:
1) Month of February is ignored
2) The value at Column F2 is as text and can not be used as Date, but it can be copied and paste to other location.
This solution might help you.
download a copy of Peter Noneley's dictionary of excel functions. This book contain almost all functions with examples and usage
