Solved

excel formula for calculation of retirement date

Posted on 2013-01-17
9
18,524 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 52

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
ClickHouse in a General Analytical Workload

We have mentioned ClickHouse in some recent posts, where it showed excellent results.

In this article on Experts Exchange, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.

 

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 52

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 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