Solved

excel formula for calculation of retirement date

Posted on 2013-01-17
9
18,118 Views
Last Modified: 2013-01-18
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
Comment
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
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
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

by:Steve
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

by:edreamers
ID: 38792220
my date format is ddmmyyy.
0
Scamming the Scammers!

Have you ever heard of Scam Baiting?
It's a highly entertaining sport that you can participate in.
Introduction to beating scammers at their own game and how you can help
Share your thoughts, ideas and experiences on the topic.
Links to top Anti-Scam resources provided.

 

Author Comment

by:edreamers
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

by:edreamers
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

by:barry houdini
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 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 38792409
Hi,

Herewith 2 versions of the formula

Regards
RetirementCalc.xls
0
 
LVL 24

Accepted Solution

by:
Steve earned 250 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

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

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Suggested Solutions

Outlook Free & Paid Tools
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question