Ask Experts Questions for FREE Help !
Ask
    luissotoii's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #1

    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?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    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's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #3

    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    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's Avatar
    KISS Posts: 12,510, Reputation: 839
    Uber Member
     
    #5

    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's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #6

    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's Avatar
    ScottGem Posts: 64,970, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #7

    Jun 14, 2010, 11:25 AM
    Quote Originally Posted by luissotoii View Post
    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's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #8

    Jun 14, 2010, 11:31 AM

    I have tried the function year different possibilities. Do you use the YEAR, DATE or other function?
    ScottGem's Avatar
    ScottGem Posts: 64,970, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #9

    Jun 14, 2010, 11:50 AM

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

    =((TODAY()-A1)/365)+((TODAY()-B1)/365)
    luissotoii's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #10

    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's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #11

    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's Avatar
    ScottGem Posts: 64,970, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #12

    Jun 14, 2010, 01:40 PM

    Format the column for General, not date
    luissotoii's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #13

    Jun 15, 2010, 05:49 AM
    Perfect thank you, changing the format to general activated the formula.
    ScottGem's Avatar
    ScottGem Posts: 64,970, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #14

    Jun 15, 2010, 05:51 AM
    Quote Originally Posted by luissotoii View Post
    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's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #15

    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's Avatar
    luissotoii Posts: 15, Reputation: 1
    New Member
     
    #16

    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.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

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