If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#23
|
|||
|
|||
Days to next anniversary
Ok....I think I may have solved that issue:
=IF(A1,LOOKUP(365,DATE(YEAR(NOW())+{0,1},MONTH(A1) ,DAY(A1)-1)+1-TODAY()),"") (Amusing....all this fuss to accommodate a one-day-every-four-years occurrence.) *********** Regards, Ron XL2002, WinXP "Rick Rothstein (MVP - VB)" wrote: No, you are not dense... there is a problem, but it is not year-long like I first thought... it occurs for a start date of February 29th of a leap year with a TODAY date between January 1st and February 28th of a non leap year. Hire Date: 29-FEB-2004 (or any leap year) Today: 27-FEB-2007 (or any non leap year) Calculated Difference = 2 days Hire Date: 1-MAR-2004 (or any leap year) Today: 27-FEB-2007 (or any non leap year) Calculated Difference = 2 days Both Hire dates produce the same difference. I mistakenly thought that carried throughout the year, but the problem actually is how to handle the collapsing of the leap day into years where that date does not exist. Rick "Ron Coderre" wrote in message ... Perhaps I'm being dense....but.... Hire Date: 01-MAR-2000 Today : 27-FEB-2007 Per the formula: 2 days until the next anniversary date The math: Hire Date Anniversary: 01-MAR-2007 Less: Today's date : 27-FEB-2007 Equals : 2 days Can you clarify what you discovered? *********** Regards, Ron XL2002, WinXP "Rick Rothstein (MVP - VB)" wrote: We can trim a couple mo =IF(A1,LOOKUP(365,((TEXT(A1,"m/d/")&YEAR(NOW())+{0,1})-TODAY())),"") Startin' to look pretty good! Upon further testing of my previous offering I find it fails if the hire date is a leap day so I'll put that in the round file and "stash" this beauty cooked up by Ron. Nope, it is not starting to look good at all... this formula, and Ron's original, also 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. Unfortunately, that is the least of the problems... 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 |
Thread Tools | |
Display Modes | |
|
|