Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    New Zealand
    Posts
    541
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Dates (97 SR-2(1))

    If I have two columns of dates and in any row I subtract one date from the other, I get a numerical answer. Is there a formula I can use to convert this numerical date to years and months?

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dates (97 SR-2(1))

    There is an undocumented function, DATEDIF, which you can use.

    DATEDIF(startdate,enddate,interval)
    startdate = earliest date
    enddate = I'm sure you can guess
    both dates are in standard excel format
    interval - this is a code that must include the quote marks. The options are:
    "y" The number of complete years between the 2 dates
    "m" The number of complete months
    "d" The number of complete days
    "ym" The number of months between the dates ignoring the years
    "yd" The number of complete days between the dates ignoring the years
    "md" The number of days between the 2 dates ignoring years & months (This will give an answer of less than 31)

    In your case us the function twice, first time use interval "y" to get the years & then "ym" to get the months.

    Hope my description is not too confusing, honestly it is a quite straight forward function.

    Tony

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (97 SR-2(1))

    You could use Datedif to determine the number of months and divide by 12, rather than calling it twice.

    Jon

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Location
    Belgium
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (97 SR-2(1))

    You don't need a formula, but a FORMAT. Toolbar: Format | Cells | Number Tab | Date. There you'll find a series of pre-formatted formats, but you can add your own also.

Posting Permissions

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