Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi loungers....I am having a small problem with a formula that I use to calculate ages....I start with a birthdate in column B, and in column C have the date when the person turns age 70.....in col C, the date of turning 70 doesn't always fall on the same birth'day'.....see attached sample.....I am sure is has something to do with leap years, even though I am multiplying 70 years x 365.25 days (to account for leap years)......and in col D, I tried using 70*365.258, but that reversedthe problem....see sample......is there some formula that I can use that 'compensates' for this or, perhaps sees it as an error and willl adjust for it? Thanks for any help.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='dmcnab' post='776291' date='21-May-2009 11:05']Hi loungers....I am having a small problem with a formula that I use to calculate ages....I start with a birthdate in column B, and in column C have the date when the person turns age 70.....in col C, the date of turning 70 doesn't always fall on the same birth'day'.....see attached sample.....I am sure is has something to do with leap years, even though I am multiplying 70 years x 365.25 days (to account for leap years)......and in col D, I tried using 70*365.258, but that reversed the problem....see sample......is there some formula that I can use that 'compensates' for this or, perhaps sees it as an error and willl adjust for it? Thanks for any help.[/quote]
    Where the birthdate is in cell B1, why not use =DATE(YEAR(B1)+70,MONTH(B1),DAY(B1)) to calculate 70th birthday
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    That works very nicely, John...thank you

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='JohnBF' post='776297' date='22-May-2009 04:15']Where the birthdate is in cell B1, why not use =DATE(YEAR(B1)+70,MONTH(B1),DAY(B1)) to calculate 70th birthday[/quote]
    Just in case the birthdate was on 29 February, a more reliable formulation is =MIN(DATE(YEAR(B1)+70,MONTH(B1)+1-{1,0},DAY(B1)*{1,0}))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Paul--thank you for that neat variation----I hadn't even thought of that, but it is something that I will build into the formula....have a good weekend

Posting Permissions

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