A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Days to next anniversary



 
 
Thread Tools Display Modes
  #31  
Old July 9th, 2007, 05:04 PM posted to microsoft.public.excel.misc
Ron Coderre
external usenet poster
 
Posts: 620
Default 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




  #32  
Old July 9th, 2007, 05:31 PM posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\)
external usenet poster
 
Posts: 1,319
Default Days to next anniversary

Yes, it looks like that takes care of the error generated for a February
29th start date (and it still is significantly shorter than the formula I
postedg); but the (probably unsolvable) problem of how to deal with
February 29th start dates when the TODAY value is before March 1st of a non
leap year remains. I guess a February 29th and March 1st leap year start
dates will always be the same number of days away from the TODAY date for
that condition. Perhaps the best way to handle this is never set a start
date of leap year... make it company policy to push a February 29th start
date to the first business day on or after March 1st.

Rick


"Ron Coderre" wrote in message
...
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





  #33  
Old July 10th, 2007, 01:54 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Days to next anniversary

To fix this you'd need to subtract 1 if the start date is a leap day and
YEAR(TODAY()) is a non-leap year. What I came up with to do this is just as
long as the original formula:

-AND(MONTH(A2)=2,DAY(A2)=29,ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, it looks like that takes care of the error generated for a February
29th start date (and it still is significantly shorter than the formula I
postedg); but the (probably unsolvable) problem of how to deal with
February 29th start dates when the TODAY value is before March 1st of a
non leap year remains. I guess a February 29th and March 1st leap year
start dates will always be the same number of days away from the TODAY
date for that condition. Perhaps the best way to handle this is never set
a start date of leap year... make it company policy to push a February
29th start date to the first business day on or after March 1st.

Rick


"Ron Coderre" wrote in message
...
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







  #34  
Old July 10th, 2007, 02:51 AM posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\)
external usenet poster
 
Posts: 1,319
Default Days to next anniversary

While you can subtract (or even add) 1 to the start date, I just don't know
that there is a "fix" for the problem. In a non-leap year, there is no
February 29th; so if the start date is February 29th, you are stuck
considering it as February 28th (last day of the month stays last day of the
month) or as March 1st (the day after February 28th stays the day after
February 28th) for non leap years... there is no "correct" solution for the
problem... a company will just have to make a policy decision on how they
are willing to collapse February 29th into non leap years (or, as I
suggested sort of tongue-in-cheek earlier, simply never allow February 29th
to be a start date for an employee).

Rick


"T. Valko" wrote in message
...
To fix this you'd need to subtract 1 if the start date is a leap day and
YEAR(TODAY()) is a non-leap year. What I came up with to do this is just
as long as the original formula:

-AND(MONTH(A2)=2,DAY(A2)=29,ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, it looks like that takes care of the error generated for a February
29th start date (and it still is significantly shorter than the formula I
postedg); but the (probably unsolvable) problem of how to deal with
February 29th start dates when the TODAY value is before March 1st of a
non leap year remains. I guess a February 29th and March 1st leap year
start dates will always be the same number of days away from the TODAY
date for that condition. Perhaps the best way to handle this is never set
a start date of leap year... make it company policy to push a February
29th start date to the first business day on or after March 1st.

Rick


"Ron Coderre" wrote in message
...
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








  #35  
Old July 10th, 2007, 04:48 AM posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\)
external usenet poster
 
Posts: 1,319
Default Days to next anniversary

-AND(MONTH(A2)=2,DAY(A2)=29,ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))

That non-leap year test of yours...

ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))

Why so many characters?

ISERR(VALUE("2/29/"&YEAR(now())))

Savings? Eleven characters (sorry, couldn't resist). g

Yes, I properly should be using DATEVALUE but, for the test being performed,
I am pretty sure VALUE will work fine.

Rick

  #36  
Old July 10th, 2007, 05:20 AM posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\)
external usenet poster
 
Posts: 1,319
Default Days to next anniversary

-AND(MONTH(A2)=2,DAY(A2)=29,ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))

That non-leap year test of yours...

ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))

Why so many characters?

ISERR(VALUE("2/29/"&YEAR(now())))

Savings? Eleven characters (sorry, couldn't resist). g


More straight forward than the above...

(MONTH(DATE(YEAR(NOW()),2,29))=3)

Same number of characters though :-(

Rick
  #37  
Old July 10th, 2007, 05:44 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Days to next anniversary

I almost always go with what first pops into my head and after I've commited
myself is when I start thinking of alternatives.

Like:

=ISERR(--("2/29/"&YEAR(NOW())))

There seems to be almost no limit when it comes to "building" dates. We
discussed this a while back. I was playing around and discoverd that stuff
like this works but it shouldn't (at least you would think that it
shouldn't):

A1 = July

=MONTH(A1&1)

A1 = July1

=MONTH(A1)

A1 = July0

=MONTH(A1)

People started chimming in that they also never realized this sort of stuff
worked.

I finally understand what you mean about that 2/29 vs 3/1 deal. I think
adding 1 to the start date (if a leap day) is the way to go and be done with
it!

If someone was born on 2/29/2000 when do they *legally* become 1 year old?
2/28/2001 or 3/1/2001?

Damn those leap years! g

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
-AND(MONTH(A2)=2,DAY(A2)=29,ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))


That non-leap year test of yours...

ISERR(--TEXT("2/29/"&YEAR(NOW()),"m/d/yy")))

Why so many characters?

ISERR(VALUE("2/29/"&YEAR(now())))

Savings? Eleven characters (sorry, couldn't resist). g

Yes, I properly should be using DATEVALUE but, for the test being
performed, I am pretty sure VALUE will work fine.

Rick



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.