Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# excel formula for calculation of retirement date

Posted on 2013-01-17
Medium Priority
18,961 Views
i want to calculate retirement age of employees from date of birth. I have date of birth of employees.  Retirement age is 60 years.

If employees date of birth is after 1 January and before 2 July (i.e 2 January to 1 July) then retirement date will be 30 june 20xx. and if employees date if birth is after 1 july and
before 2 January (i.e. 2 July to 1 January) then the retirement date will be 31 December 20xx.

Kindly help, i have a excel sheet in which i am calculating this data.
0
Question by:edreamers
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2
• 2
• +1

LVL 53

Expert Comment

ID: 38792016
Hi,

You could try this

=DATEVALUE(YEAR(EOMONTH(A1-1;720))&"/"&ROUNDUP((MONTH(EOMONTH(A1-1;720))/6);0)*6&"/"&IF(ROUNDUP((MONTH(EOMONTH(A1-1;720))/6);0)*6=6;30;31))

Regards
0

LVL 24

Expert Comment

ID: 38792101
Another solution:

=IF(MONTH(A1-1)>6,DATE(YEAR(A1-1)+60,12,30),DATE(YEAR(A1-1)+60,6,30))
0

Author Comment

ID: 38792220
my date format is ddmmyyy.
0

Author Comment

ID: 38792231
Thanks for reply Rgonzo1971. But the expression is not working. It gives error  msg "The formula you typed contains an error"
0

Author Comment

ID: 38792233
Thanks for reply The_Barman. But the expression is not working. It give error -- #VALUE!

My date format is ddmmyyyy
0

LVL 50

Expert Comment

ID: 38792407
You haven't really given a full explanation - if an employee's DOB is 1st Jan then requirement date is 31st December......or which year? Should that be 1 day before 60th birthday or 1 day before 61st birthday?

The_Barman's formula works for me, assuming day before 60th birthday in my example, (although first 30 in formula should be 31), or you can use this formula

=DATE(YEAR(A1-1)+60,CEILING(MONTH(A1-1),6)+1,0)

However both of those work with valid dates, if you just have a number like 29081952 (representing 29th August 1952) then those won't work.

If you have a column of dates in that format then try converting to "true" dates using "text to columns" functionality

Select column of dates > Data > Text to columns > Next > Next > under "column data format" select "date" and "DMY" from the dropdown > Finish > enlarge column if required to see dat correctly. Now you should be able to use my formula above to get retirement date.

......or try using this formula to give retirement date from your original data without converting to a date

=DATE(YEAR(TEXT(A2,"00-00-0000")-1)+60,CEILING(MONTH(TEXT(A2,"00-00-0000")-1),6)+1,0)

see attached for examples of both approaches

regards, barry
Retirement-dates.xls
0

LVL 53

Assisted Solution

Rgonzo1971 earned 1000 total points
ID: 38792409
Hi,

Herewith 2 versions of the formula

Regards
RetirementCalc.xls
0

LVL 24

Accepted Solution

Steve earned 1000 total points
ID: 38792415
I am guessing you do not have an actual date, but have it in text format...

To convert text dates to dates... try the following formula with the date in A3:
=DATE(RIGHT(A3,4),MID(A3,4,2),LEFT(A3,2))  if date is dd/mm/yyyy
=DATE(RIGHT(A3,4),MID(A3,3,2),LEFT(A3,2))  if date is ddmmyyyy

Then use the original formula with the date in A1
=IF(MONTH(A1-1)>6,DATE(YEAR(A1-1)+60,12,31),DATE(YEAR(A1-1)+60,6,30))
(with the modification from barry 30 to 31 for dec, well spotted there barry, cheers)
Dates.xlsx
0

Author Closing Comment

ID: 38792553
Thanks friends, this forum is a really a life saver.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing howâ€¦
New style of hardware planning for Microsoft Exchange server.
If youâ€™ve ever visited a web page and noticed a cool font that you really liked the look of, but couldnâ€™t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yoâ€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
###### Suggested Courses
Course of the Month4 days, 10 hours left to enroll