Results 1 to 3 of 3
  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 with dates (XL97/WinNT)

    I'm probably asking for the moon here, but is there any way to get Excel to work out the number of days left until a certain date - and when that day passes, to carry on calculating the number of days until the next time it comes round? That is, calculating on dates without the year ...

    By this I mean, if I wanted to have a spreadsheet say 'Days until my birthday: 2' then '...1' then '...0' - and the next day it would say 'Days until my birthday: 365'?

    The only way I can work it is to have today's date and the target date in two cells and deduct one from the other - but then I have to change the year or it'll carry on with -1, -2, etc after the date passes.

    Any help would be gratefully appreciated!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating with dates (XL97/WinNT)

    See the attached spreadsheet.

    I've put the the current year and the birthday for this year and next year in separate cells as intermediate results to keep it transparent I hope).

    I've also put the calculation into one formula, but as you can see, it's not pretty.
    Attached Files Attached Files

  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 with dates (XL97/WinNT)

    Wow! Yes, I see what you mean about the 'not pretty' bit, but in fact I'm putting together a spreadsheet that will remind me when a number of dates are coming up (by changing colour when there's less than a month to go) so I'm quite happy to have separate cells as per your first way.

    Many, many thanks for that, Hans!
    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
  •