Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Formula, Elapsed Y and M (XL03)

    Hi Ricky

    Does this post <post#=687,387>post 687,387</post#> help you at all?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Formula, Elapsed Y and M (XL03)

    Thanks Steve, I think it does. Wasn't at all familiar with the DATEDIF function. I think I can adapt the example from your posting and make it work. And just in time too... I just discovered a problem with my formula; if the target cell is blank, the result is 108 years. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> I'd have to add another "IF" statement to handle the blank (or zero) cell.

    Thanks again.
    - Ricky

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Date Formula, Elapsed Y and M (XL03)

    I enter a hire date in a cell (E9) and in another cell, I want a formula whose result will show the number of years and months the employee has been with the company. Formatted like: "9 yrs. & 6 mos."
    The following formula seems to work but man it's uuuuugly. I'm just curious if there was more attractive way to achieve the desired result?

    <pre>=IF(TRUNC((NOW()-E9)/365)<1,CONCATENATE(TEXT(INT(MOD(NOW()-E9,365)/31),"0")," Mos."),
    CONCATENATE(TEXT(TRUNC((NOW()-E9)/365),"0")," Yrs."," & ",TEXT(INT(MOD(NOW()-E9,365)/31),"0")," mos."))</pre>


    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    - Ricky

Posting Permissions

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