Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: excel formula

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Excelers,

    I am trying to figure out how solve the following problem: I have to keep track of boys' ages as they change every month and have the information at my finger tips. I set up a spread sheet as below, from January to December of that year.

    DOB = date of birth and 15.08 means 15 years & 8 months old - 1/1/02=the date of the month

    Column Column Column etc. etc.
    A B C D E
    Name DOB 1/1/02 2/1/02 3/1/02
    Jones, R. 3/28/86 15.10 15.11 15.12
    (For some reason, when my message is posted all the information right above is crunched together to on the left side and may not make sense to anyone)

    My first problem is that I want 15.12 to turn to 16.00, not 15.12 and continue 16.01, 16.02 and so on.

    I have been using the following formula
    =C2+0.01, =C2+0.02, etc. This works fine until it comes to converting the 15.11 into 16.00. It just changes into 15.12 and I also can't figue out how to go on to the next January.

    The second problem is not knowing what to do when I get to December of the year. How do I get back or go on to January of the next year without having to reenter all the data for 200 plus boys? The list will only continual to grow.

    A gentleman suggested that I try the following formula:

    =if(MOD(C2,1)=.11,C2+.89,c2+.01)

    When I tried it the excel program told me it was not correct and did I want them to correct the formula. I clicked yes and it changed the formula to:

    =IF(MOD(C2,1)=0.11,C2+K4.89,C2+0.01

    When I copied it in the next cell it changed the formula according to the column I was in, but when it got to the 15.12 column it did not change it into 16.00.

    First, I would like to know what this formula is saying, and Secondly, I don't know why it is not working.

    Thanks in advance for any guidance anyone can give me!

    Rich










    [ This Message was edited by: Russellgram on 2002-03-09 20:59 ]

    [ This Message was edited by: Russellgram on 2002-03-09 21:04 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    friend,

    download file..
    "agetime"

    i have use the function datedif provided by exel..

    http://www.pexcel.comdownload.htm

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Russellgram

    I must admit I get a bit lost trying to read you explanation, but if you want a persons age in Years and months you would most likely be better to have the years in one column and the months in another. This would make for a better spreadsheet! Try this:

    Put the formula =TODAY() in say cell A1 this will be used to reference todays date in ALL your formulas. The reason why we dont nest the function into the others is because TODAY is a volatile function and using it to liberally will cause re-calculation slow dowm.

    No in the Column for the person age in years simply use:
    =DATEDIF($A$2,$A$1,"y")

    Now in the next Column use:

    =IF(MONTH($A$1)
    This will calculate the months.

    In both formulas the assumption is that there is a bith-date in cell A2




    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    http://www.microsoftexceltraining.com

    [ This Message was edited by: Dave Hawley on 2002-03-09 23:22 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Try Datedif
    Datedif is in Excel but it is only documented in Excel 2000.

    in one cell =DATEDIF(A77,TODAY(),"y")
    in another =DATEDIF(A77,TODAY(),"ym")

    or with named ranges
    =DATEDIF(DOB,cMonth,"y")
    =DATEDIF(DOB,cMonth,"ym")

    in one cell

    =DATEDIF(DOB,cMonth,"y")& "y "&DATEDIF(DOB,cMonth,"ym")&"m"

    You could also look at =YEARFRAC(DOB,cMonth)

    revise references as necessary.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •