excel formula for calculation of retirement date

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.
edreamersAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
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
 
Rgonzo1971Commented:
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
 
SteveCommented:
Another solution:

=IF(MONTH(A1-1)>6,DATE(YEAR(A1-1)+60,12,30),DATE(YEAR(A1-1)+60,6,30))
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
edreamersAuthor Commented:
my date format is ddmmyyy.
0
 
edreamersAuthor Commented:
Thanks for reply Rgonzo1971. But the expression is not working. It gives error  msg "The formula you typed contains an error"
0
 
edreamersAuthor Commented:
Thanks for reply The_Barman. But the expression is not working. It give error -- #VALUE!

My date format is ddmmyyyy
0
 
barry houdiniCommented:
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
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Herewith 2 versions of the formula

Regards
RetirementCalc.xls
0
 
edreamersAuthor Commented:
Thanks friends, this forum is a really a life saver.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.