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

    Ongoing dates calculation (XL97/WinNT4)

    I'm getting very confused with the various formulae available to calculate on dates - and the fact that so few of them seem to be willing to work in actual dates, and not the equivalent serial numbers!

    What I need to do is have a cell with a rolling calculation in it - basically, the number of days between now and the next time a date comes round, eg 16 August. At the moment it's 54 days (I think) - but on 17 August it becomes 364. Currently I have three cells, with today's date, the required date, and the result of deducting one from the other, but I have to update the required date cell by changing the year as the date passes, and since I have some 23 of these dates, it gets a bit irritating!

    Is it possible to have a cell that says "if [req day]+[req month]+[this year auto] - today < 0 then result+365, else result" ??!! Or is there another way of doing this?

    Any help gratefully appreciated!
    Beryl M

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Ongoing dates calculation (XL97/WinNT4)

    See the attached workbook. If you like, it could be done in fewer formulas. The idea is to use the DATE function to calculate the occurrence of, say, 16 August in this year and in the next year. Then, check whether today is past the occurrence in this year, and depending on the answer subtract today from one or the other.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Ongoing dates calculation (XL97/WinNT4)

    This formula would do it, (This is the August 16th one, replace the month and date in both locations for other dates)
    =DATE((DATE(YEAR(NOW()),8,16)<NOW())+YEAR(NOW()),8 ,16)-NOW()

    This will be updated automatically as time progresses.


Posting Permissions

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