Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel formula for calculation of retirement date

Posted on 2013-01-17
9
Medium Priority
?
19,266 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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 53

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 53

Assisted Solution

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

Herewith 2 versions of the formula

Regards
RetirementCalc.xls
0
 
LVL 24

Accepted Solution

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

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

824 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