|
|
|
|
New Member
|
|
Jun 11, 2010, 06:13 AM
|
|
We use Microsoft Office 2003 at our office. Our company computes the employees’ age,
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?
|
|
|
Software Expert
|
|
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.
|
|
|
New Member
|
|
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.
|
|
|
Software Expert
|
|
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)
|
|
|
Uber Member
|
|
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.
|
|
|
New Member
|
|
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).
|
|
|
Computer Expert and Renaissance Man
|
|
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.
|
|
|
New Member
|
|
Jun 14, 2010, 11:31 AM
|
|
I have tried the function year different possibilities. Do you use the YEAR, DATE or other function?
|
|
|
Computer Expert and Renaissance Man
|
|
Jun 14, 2010, 11:50 AM
|
|
Assume A1=DOB and B1 = DOH in cell C1 put:
=((TODAY()-A1)/365)+((TODAY()-B1)/365)
|
|
|
New Member
|
|
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.
|
|
|
New Member
|
|
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.
|
|
|
Computer Expert and Renaissance Man
|
|
Jun 14, 2010, 01:40 PM
|
|
Format the column for General, not date
|
|
|
New Member
|
|
Jun 15, 2010, 05:49 AM
|
|
Perfect thank you, changing the format to general activated the formula.
|
|
|
Computer Expert and Renaissance Man
|
|
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.
|
|
|
New Member
|
|
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.
|
|
|
New Member
|
|
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.
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Re:Microsoft office 2003
[ 7 Answers ]
My friend got a PC with Microsoft Media Edition installed on it.I've tried couple of time to install Microsoft Office 2003,it reads the disc,but if its halfway installed it gives an error message with a error number and say that the installation process can not proceed due to an error,and it need...
Microsoft Office Word 2003
[ 3 Answers ]
HELP! I have a fax cover sheet template (that I created) that I need to be able to type in information without the words moving. I tried locking it but that won't allow me to type in any information. I'm so baffled...
Ditzee:confused:
Microsoft Office 2003
[ 1 Answers ]
I can not see font colors or background colors in excel work sheets (in power point is always set to grey scale and always required to click view,color tabs to get colors). Can somebody help me?
Microsoft Office 2003 Professional
[ 1 Answers ]
I have just installed a new master hard drive in my computer, and am attempting to install my copy of Microsoft Office 2003 Professional. Unfortunately, I've lost the serial number - no, really.
I still have Office installed on the slave (used to be master) drive, and I'm wondering if there's...
View more questions
Search
|