View Single Post
  #26  
Old July 9th, 2007, 03:16 PM posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\)
external usenet poster
 
Posts: 1,319
Default Days to next anniversary

...or even a few more...

=IF(A1"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"")

(I thought it would get tripped up by a start date of 02/29/2000...
but, so far so good.)


Nope, unfortunately your "thought" was correct... this formula, and your and
Biff's subsequent modifications to it, do get tripped up by a start date of
02/29/2000 (or any February 29th in a proper leap year)... it happens
whenever the "TODAY" date is anywhere within the range of January 1st to
February 28th.

As it turns out, ALL of the formulas submitted so far (including Ron's and
its variations) will produce an incorrect calculation if the TODAY date is
anywhere within the range of January 1st to February 28th of a NON leap year
and the start date is any day on or after March 1st of a leap year... under
those conditions, the date difference will be one day less than it is
supposed to be. To test what I am saying, try a Start Date of March 1, 2000
and a TODAY date of February 27, 2007. There should be 3 days difference
between these days (Feb 28, Feb 29 and Mar 1), but all formulas report 2
days... and this missing day is carried forward for Start Dates up to and
including December 31, 2007.

Rick