Check out some similar questions!

luissotoii · Jun 11, 2010, 06:13 AM

We use Microsoft Office 2003 at our office. Our company computes the employees’ age, e.g. (01/01/1949) plus their starting date, e.g. (08/22/1989) to check if they are eligible for the 75 rule for retirement.

Can Excel compute this with the age changing daily to keep the formula current?

JBeaucaire · Jun 11, 2010, 10:54 AM

You can construct a formula to give you age, but you haven't provided enough info about how you calculate to offer anything specific.

Non-specifically, this formula will tell you how many days old a person is:

If cell A1 contains the birthdate, put this in B1:

=TODAY() - A1
... format that cell as GENERAL.

luissotoii · Jun 11, 2010, 01:09 PM

I thought there it was a two part; age counting down (FALSE) and the second date was when they started working so it would go up (TRUE).

Adding the two would offer you the number of years until 75.

JBeaucaire · Jun 11, 2010, 10:41 PM

A1 - birth date
A2 - hire date

In A3 put this formula and format cell as GENERAL:

=(DATE(YEAR(A1)+75,MONTH(A1),DAY(A1)) - A2) / 365

Alternately, to round up to the whole numbers:

=CEILING((DATE(YEAR(A1)+75,MONTH(A1),DAY(A1)) - A2) / 365, 1)

KISS · Jun 11, 2010, 11:06 PM

No one explained the 75 rule; what if the rule was age+years=75

like a person with 30 yrs service has to be 45 years old or more.

luissotoii · Jun 14, 2010, 11:12 AM

Actually it is age + years=75 for retirement unless you are 62 years old and 5 years tenure.

For excel to maintain continually update daily; the age would have to count down (decrease) AND (-) the tenure would count up (increase).

ScottGem · Jun 14, 2010, 11:25 AM

Originally Posted by luissotoii

Actually it is age + years=75 for retirement unless you are 62 years old and 5 years tenure.

For excel to maintain continually update daily; the age would have to count down (decrease) AND (-) the tenure would count up (increase).

That's not correct. If the formula is that they are eligible for retirement when their age + service = 75 years, then you simply add the number of years of service to the age. If you are looking for the date they will be eligible for retirements based on this rule that's a different matter.

luissotoii · Jun 14, 2010, 11:31 AM

I have tried the function year different possibilities. Do you use the YEAR, DATE or other function?

ScottGem · Jun 14, 2010, 11:50 AM

Assume A1=DOB and B1 = DOH in cell C1 put:

=((TODAY()-A1)/365)+((TODAY()-B1)/365)

luissotoii · Jun 14, 2010, 12:13 PM

Actually having an abutting column designating the eligible year would be nice.

I have attempted the YEARS and DATE function with unsuccessfully.

luissotoii · Jun 14, 2010, 12:25 PM

When I input
=((TODAY()-A1)/365)+((TODAY()-B1)/365)
I continually get 5/16/2010 18:52 and other similar values.

ScottGem · Jun 14, 2010, 01:40 PM

Format the column for General, not date

luissotoii · Jun 15, 2010, 05:49 AM

Perfect thank you, changing the format to general activated the formula.

ScottGem · Jun 15, 2010, 05:51 AM

Originally Posted by luissotoii

Perfect thank you, changing the format to general activated the formula.

It didn't "activate" the formula, just displayed the results in a format that you want.

luissotoii · Jun 15, 2010, 05:55 AM

I added a conditional format to highlight employees over 75 and it works great. Again thank you for your patience.

luissotoii · Jun 17, 2010, 05:55 AM

A new parameter is employees are prohibited to retire until they reach 50 although they met the 75-year rule. E.G. I have information in columns A-F; in column G I posted the =((TODAY()-D2)/365)+((TODAY()-G2)/365) function with Cell Value Is conditional formatting. In attempting Formula Is formatting I wrote = COUNTIF$D2:$D1203, ((TODAY()-D2)/365<50) unsuccessfully. Any suggestions.