Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Calculating on dates (O2K)

    I'm sure this is just me being stupid, but it's driving me cracked! How do I get XL to calculate using dates? I want to tell it to deduct eg 1994 from this year, to get how many years something has been in existence, and can I work out how?!

    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> I can get it to show me the date, but I can't get it to calculate using it! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Please, someone help me! I've only just upgraded to O2K ...
    Beryl M


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

    Re: Calculating on dates (O2K)

    First things first. 1994 is not a date, it is a number. If you want it to be a date, then you will have to make it a date by entering 1/1/1994.

    If the starting date is in A1 and the ending date is in B1, then you can use =B1-A1 to get the number of days between those two dates. You can convert that to years by using =(B1-A1)/365.25.

    There is also an undocumented function to get the difference between two dates. The following will calculate the number of whole years between the two dates in A1 and B1:

    =DATEDIF(A1,B1,"y")
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating on dates (O2K)

    Thanks, Legare, and apologies for mistaking numbers for dates! The answer you give is helpful, but in this case I was thinking more along the lines of taking the value of the current year (ie 2001) as a number, in order to deduct 1994 from it and get the answer which, although a number rather than a date, would tell me how many years since 1994 (the year!). This is because the cell from which it's taking the number '1994' is just typed in ... any suggestions? Your efforts are much appreciated!
    Beryl M


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

    Re: Calculating on dates (O2K)

    If 1994 is in A1, then:

    <pre>=YEAR(TODAY())-A1
    </pre>

    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculating on dates (O2K)

    That was the one! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    I had tried today, and year, and several others, but could I get them to work?! I now see I needed the extra set of empty brackets ...

    Many thanks! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


Posting Permissions

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