Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Years between two dates

    I have a set of starting dates that I want to compare to today's date, to show years of service. I want the answer in whole numbers, such as 10 for 10 years. How does that formula look?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Years between two dates

    <pre>=DATEDIF(A1,NOW(),"y")</pre>


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years between two dates

    I need more information about the formula. What goes in the parens? What is A1?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Years between two dates

    A1 is the cell which has a starting date (change as appropriate). Once entered it can be copied down a column or across the rows.

    Nothing needs to go in the now() parentheses. Now() is just a function which gives the current date/time. The "y" tells the formula to put it in years.

    Chip Pearson has an excellent discussion of the DATEDIF Function with many examples and possible uses...

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years between two dates

    Thank you. My target column is formatted as dates. I copied the formula down. All the results are 0.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years between two dates

    Any idea how to NOT get zeroes for all my answers? There are time spans between the dates.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years between two dates

    That could be caused by a number of things. Could you upload a small workbook that shows the problem you are having?
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years between two dates

    Here's a sample notebook.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years between two dates

    Sorry, I do not see a problem. I could not find where you had used the formula that Steve gave you anywhere on the sheet, therefore do not see what your problem is. I entered a modification (to account for empty cells) of Steve's formula into columns I, J, and K and they all seem to calculate correctly. See the attached workbook.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Years between two dates

    Thanks! I had deleted the non-working formula. Yours works fine.

Posting Permissions

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