Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Days to next anniversary

I want to show the number of days between the current date (today) and the
hiring date in order to provide an indication of how many days is the
anniversary away. Any suggestions?

Thanks, Stefano
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 427
Default Days to next anniversary

This worked for me: with the anniversary date in cell A1,
=IF(MONTH(TODAY())<=MONTH(A1),DATE(YEAR(TODAY()),M ONTH(A1),DAY(A1))-
TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY())

The formula adjusts the anniversary date to the same day in the
current year if that month has not occurred yet, and to next year if
the anniversary month occurred already.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default Days to next anniversary

"Dave O" wrote in message
oups.com...
This worked for me: with the anniversary date in cell A1,
=IF(MONTH(TODAY())<=MONTH(A1),DATE(YEAR(TODAY()),M ONTH(A1),DAY(A1))-
TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY())

Returns -7 to -1 for dates July 1 2007 to July 7 2007 respectively.

My suggestion would be:

=DATEDIF(TODAY(),MAX(A1,DATE(YEAR(TODAY())+(DATE(Y EAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1) ,DAY(A1))),"d")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Days to next anniversary

Try this:

A2 = hire date

=IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY()))

--
Biff
Microsoft Excel MVP


"smaruzzi" wrote in message
...
I want to show the number of days between the current date (today) and the
hiring date in order to provide an indication of how many days is the
anniversary away. Any suggestions?

Thanks, Stefano



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default Days to next anniversary

"T. Valko" wrote in message
...
A2 = hire date

=IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY()))

Returns 358 for date in A2 of June 30 2007 and 7 to 1 for July 1 2007 to
July 7 2007 respectively. Why return an empty string for dates in the
future?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
Try this:

A2 = hire date

=IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY()))

--
Biff
Microsoft Excel MVP


"smaruzzi" wrote in message
...
I want to show the number of days between the current date (today) and the
hiring date in order to provide an indication of how many days is the
anniversary away. Any suggestions?

Thanks, Stefano








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default Days to next anniversary

Thanks to each one of you. I think Sandy's suggestion is the correct one.

Stefano

"Sandy Mann" wrote:

"Dave O" wrote in message
oups.com...
This worked for me: with the anniversary date in cell A1,
=IF(MONTH(TODAY())<=MONTH(A1),DATE(YEAR(TODAY()),M ONTH(A1),DAY(A1))-
TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY())

Returns -7 to -1 for dates July 1 2007 to July 7 2007 respectively.

My suggestion would be:

=DATEDIF(TODAY(),MAX(A1,DATE(YEAR(TODAY())+(DATE(Y EAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1) ,DAY(A1))),"d")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Days to next anniversary

"Sandy Mann" wrote in message
...
"T. Valko" wrote in message
...
A2 = hire date

=IF(OR(A2="",A2TODAY()),"",ABS(DATE(YEAR(TODAY()) +(MONTH(TODAY())MONTH(A2)),MONTH(A2),DAY(A2))-TODAY()))

Returns 358 for date in A2 of June 30 2007 and 7 to 1 for July 1 2007 to
July 7 2007 respectively.


Ooops! Back to the drawing board!

Why return an empty string for dates in the future?


I'm thinking that the hire date can't be today. You don't have an
anniversary date until you actually have a start date.

--
Biff
Microsoft Excel MVP



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default Days to next anniversary

I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many days
is the anniversary away. Any suggestions?


Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this formula
is the shortest one offered so far except for Biff's, mine is one character
longer than Biff's formula; but, as Sandy pointed out, Biff will have to
modify his formula to account for the problem Sandy has pointed out, so we
will see how things shake out on the character count later.

Rick

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default Days to next anniversary

While character count is not an absolute metric...

That was supposed to say...

"While character count is not an absolute metric to necessarily strive
for..."

Rick



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default Days to next anniversary

Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")


Which, in comparing it to Sandy's, is the same except that mine eliminates
the call to the MAX function that Sandy used.

Rick

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Days to next anniversary

Bonsour® Rick Rothstein (MVP - VB) avec ferveur ;o))) vous nous disiez :

Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")


May be this formula is shortest ??? :
=DATE( YEAR(TODAY()) + (DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()) ,
MONTH(A1) , DAY(A1)) - TODAY()

HTH

--
@+
;o)))


  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Days to next anniversary

Character count? Where's your error checking? <g

I was trying to come up with a shortcut for:

+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()

But this didn't work (I didn't test it enough):

+(MONTH(TODAY())MONTH(A2))

This seems to work and I still retain the character count title <g:

=ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())

But I still like having some error checking:

=IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many days
is the anniversary away. Any suggestions?


Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this formula
is the shortest one offered so far except for Biff's, mine is one
character longer than Biff's formula; but, as Sandy pointed out, Biff will
have to modify his formula to account for the problem Sandy has pointed
out, so we will see how things shake out on the character count later.

Rick



  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Days to next anniversary

Let's trim some more characters:

No longer need the call to ABS:

=DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Character count? Where's your error checking? <g

I was trying to come up with a shortcut for:

+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()

But this didn't work (I didn't test it enough):

+(MONTH(TODAY())MONTH(A2))

This seems to work and I still retain the character count title <g:

=ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())

But I still like having some error checking:

=IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many days
is the anniversary away. Any suggestions?


Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this
formula is the shortest one offered so far except for Biff's, mine is one
character longer than Biff's formula; but, as Sandy pointed out, Biff
will have to modify his formula to account for the problem Sandy has
pointed out, so we will see how things shake out on the character count
later.

Rick







  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
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.)
***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

Let's trim some more characters:

No longer need the call to ABS:

=DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Character count? Where's your error checking? <g

I was trying to come up with a shortcut for:

+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()

But this didn't work (I didn't test it enough):

+(MONTH(TODAY())MONTH(A2))

This seems to work and I still retain the character count title <g:

=ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())

But I still like having some error checking:

=IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many days
is the anniversary away. Any suggestions?

Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this
formula is the shortest one offered so far except for Biff's, mine is one
character longer than Biff's formula; but, as Sandy pointed out, Biff
will have to modify his formula to account for the problem Sandy has
pointed out, so we will see how things shake out on the character count
later.

Rick






  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default Days to next anniversary

...or....assuming text and future values wouldn't be entered as a start date

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

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

...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.)
***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

Let's trim some more characters:

No longer need the call to ABS:

=DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Character count? Where's your error checking? <g

I was trying to come up with a shortcut for:

+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()

But this didn't work (I didn't test it enough):

+(MONTH(TODAY())MONTH(A2))

This seems to work and I still retain the character count title <g:

=ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())

But I still like having some error checking:

=IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many days
is the anniversary away. Any suggestions?

Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this
formula is the shortest one offered so far except for Biff's, mine is one
character longer than Biff's formula; but, as Sandy pointed out, Biff
will have to modify his formula to account for the problem Sandy has
pointed out, so we will see how things shake out on the character count
later.

Rick





  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Days to next anniversary

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.

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
..or....assuming text and future values wouldn't be entered as a start
date

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

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

...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.)
***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

Let's trim some more characters:

No longer need the call to ABS:

=DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Character count? Where's your error checking? <g

I was trying to come up with a shortcut for:

+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()

But this didn't work (I didn't test it enough):

+(MONTH(TODAY())MONTH(A2))

This seems to work and I still retain the character count title <g:

=ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())

But I still like having some error checking:

=IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in
message ...
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many
days
is the anniversary away. Any suggestions?

Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this
formula is the shortest one offered so far except for Biff's, mine
is one
character longer than Biff's formula; but, as Sandy pointed out,
Biff
will have to modify his formula to account for the problem Sandy has
pointed out, so we will see how things shake out on the character
count
later.

Rick







  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Days to next anniversary

Argh!

Disregard that formula. It fails if the hire date is a leap day.

See Ron's beauty!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Let's trim some more characters:

No longer need the call to ABS:

=DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Character count? Where's your error checking? <g

I was trying to come up with a shortcut for:

+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()

But this didn't work (I didn't test it enough):

+(MONTH(TODAY())MONTH(A2))

This seems to work and I still retain the character count title <g:

=ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())

But I still like having some error checking:

=IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many days
is the anniversary away. Any suggestions?

Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this
formula is the shortest one offered so far except for Biff's, mine is
one character longer than Biff's formula; but, as Sandy pointed out,
Biff will have to modify his formula to account for the problem Sandy
has pointed out, so we will see how things shake out on the character
count later.

Rick







  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Days to next anniversary

Hi Ron

Very nice solution.
Just need to remember that the whole world doesn't use US date
formats!<vbg.
It gives some strange results in the UK unless you modify to
=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition to Biff'sNOW() in place of TODAY() you can trim another 2 characters by using"m/d/" or "d/m/" in place of the double dd and double mm--RegardsRoger Govier"Ron Coderre" wrote in ... ..or....assuming text and future values wouldn't be entered as a startdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: ...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 of02/29/2000...but, so far so good.) *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Let's trim some more characters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count? Where's your error checking? <g I was trying to come up with a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY() But this didn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I still retain the character count title<g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some error checking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+ (DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... I want to show the number of days between the current date(today) and the hiring date in order to provide an indication of howmany days is the anniversary away. Any suggestions? Here is my offering (assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(T ODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY( A1)),"d") While character count is not an absolute metric, I note thatthis formula is the shortest one offered so far except for Biff's,mine is one character longer than Biff's formula; but, as Sandy pointed out,Biff will have to modify his formula to account for the problem Sandyhas pointed out, so we will see how things shake out on thecharacter count later. Rick



  #21   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Days to next anniversary

Not sure what happened with that posting???. Hope this comes out
clearer.

Hi Ron

Very nice solution.
Just need to remember that the whole world doesn't use US date
formats!<vbg.

It gives some strange results in the UK unless you modify to
=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition to Biff'sNOW() in place of TODAY() you can trim another 2 characters byusing"m/d/" or "d/m/" in place of the double dd and double mm--RegardsRoger Govier"Roger Govier" wrote in l... Hi Ron Very nice solution. Just need to remember that the whole world doesn't use US dateformats!<vbg. It gives some strange results in the UK unless you modify to=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition toBiff'sNOW() in place of TODAY() you can trim another 2 characters byusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Ron wrote text and future values wouldn't be entered as astartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "RonCoderre" wrote: ...or even a fewmore...=IF(A1<"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start dateof02/29/2000...but, so far so good.) *********** Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trim some morecharacters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count?Where's your error checking? <g I was trying to come upwith a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY() But thisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I stillretain the character count title<g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some errorchecking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW()) +(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP -VB)" wrote in .. . I want to show thenumber of days between the current date(today) and the hiringdate in order to provide an indication of howmany days is theanniversary away. Any suggestions? Here is my offering(assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR( TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY (A1)),"d") While charactercount is not an absolute metric, I note thatthis formula is theshortest one offered so far except for Biff's,mine is one character longer than Biff's formula; but, as Sandy pointed out,Biff will have to modify his formula to account for the problem Sandyhas pointed out, so we will see how things shake out on thecharactercount later. Rick

  #22   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Days to next anniversary

Still messed up, even though clear on my screen before posting. Last
attempt.

Hi Ron

Very nice solution.
Just need to remember that the whole world doesn't use US date
formats!<vbg.
It gives some strange results in the UK unless you modify to

=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition to Biff'sNOW() in place of TODAY()you can trim another 2 characters by using "m/d/" or "d/m/" in placeof the double "dd "and double "mm".--RegardsRoger Govier"Roger Govier" wrote in l... Not sure what happened with that posting???. Hope this comes outclearer. Hi Ron Very nice solution. Just need to remember that the whole world doesn't use US dateformats!<vbg. It gives some strange results in the UK unless you modify to=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition toBiff'sNOW() in place of TODAY() you can trim another 2 charactersbyusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Roger Govier" wrote in l... HiRon Very nice solution. Just need to remember that the whole worlddoesn't use US dateformats!<vbg. It gives some strange results in theUK unless you modifyto=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in additiontoBiff'sNOW() in place of TODAY() you can trim another 2 charactersbyusing"m/d/" or "d/m/" in place of the double dd anddoublemm--RegardsRoger t.com text and future values wouldn't be entered asastartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP"RonCoderre" wrote: ...or even afewmore...=IF(A1<"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a startdateof02/29/2000...but, so far so good.) ***********Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trimsome morecharacters: No longer need the call to ABS:=DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in bl... Charactercount?Where's your error checking? <g I was trying to comeupwith a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY() Butthisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and Istillretain the character count title<g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)& "/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having someerrorchecking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW ())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein(MVP -VB)" wrote in . .. I want toshow thenumber of days between the current date(today) and thehiringdate in order to provide an indication of howmany days istheanniversary away. Any suggestions? Here is myoffering(assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(Y EAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1) ,DAY(A1)),"d") Whilecharactercount is not an absolute metric, I note thatthis formula is theshortest one offered so far except for Biff's,mine isone character longer than Biff's formula; but, as Sandy pointedout,Biff will have to modify his formula to account for theproblem Sandyhas pointed out, so we will see how things shake outon thecharactercount later. Rick

  #23   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default Days to next anniversary

Thanks, Roger

and, very true about the date format....But, I had it in the back of my mind
that the formula could easily be tweaked for non-US date formats. Hopefully,
that holds true.

***********
Best Regards,
Ron

XL2002, WinXP


"Roger Govier" wrote:

Not sure what happened with that posting???. Hope this comes out
clearer.

Hi Ron

Very nice solution.
Just need to remember that the whole world doesn't use US date
formats!<vbg.

It gives some strange results in the UK unless you modify to
=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition to Biff'sNOW() in place of TODAY() you can trim another 2 characters byusing"m/d/" or "d/m/" in place of the double dd and double mm--RegardsRoger Govier"Roger Govier" wrote in l... Hi Ron Very nice solution. Just need to remember that the whole world doesn't use US dateformats!<vbg. It gives some strange results in the UK unless you modify to=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in addition toBiff'sNOW() in place of TODAY() you can trim another 2 characters byusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Ron wrote

text and future values wouldn't be entered as astartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "RonCoderre" wrote: ...or even a fewmore...=IF(A1<"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start dateof02/29/2000...but, so far so good.) *********** Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trim some morecharacters: No longer need the call to ABS: =DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY() -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Character count?Where's your error checking? <g I was trying to come upwith a shortcut for:
+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY() But thisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I stillretain the character count title<g: =ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()) But I still like having some errorchecking: =IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW()) +(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP -VB)" wrote in .. . I want to show thenumber of days between the current date(today) and the hiringdate in order to provide an indication of howmany days is theanniversary away. Any suggestions? Here is my offering(assumes "hire date" is in A1)...
=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR( TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY (A1)),"d") While charactercount is not an absolute metric, I note thatthis formula is theshortest one offered so far except for Biff's,mine is one character longer than Biff's formula; but, as Sandy pointed out,Biff will have to modify his formula to account for the problem Sandyhas pointed out, so we will see how things shake out on thecharactercount later. Rick



  #24   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default Days to next anniversary

Thanks, Biff

BTW...I wasn't trying to concoct the shortest possible formula. It's just
that formulas that wrap to the next line always make me wince (It's probably
just a harmless character flaw <g). Anyway, I usually stop tweaking if it
gets to one line.

***********
Regards,
Ron

XL2002, WinXP


"T. Valko" 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.

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
..or....assuming text and future values wouldn't be entered as a start
date

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

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

...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.)
***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

Let's trim some more characters:

No longer need the call to ABS:

=DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Character count? Where's your error checking? <g

I was trying to come up with a shortcut for:

+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()

But this didn't work (I didn't test it enough):

+(MONTH(TODAY())MONTH(A2))

This seems to work and I still retain the character count title <g:

=ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY())

But I still like having some error checking:

=IF(OR(A1="",A1TODAY()),"",ABS(DATE(YEAR(NOW())+( DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in
message ...
I want to show the number of days between the current date (today)
and the hiring date in order to provide an indication of how many
days
is the anniversary away. Any suggestions?

Here is my offering (assumes "hire date" is in A1)...

=DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(YEAR(TOD AY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1),DAY(A1 )),"d")

While character count is not an absolute metric, I note that this
formula is the shortest one offered so far except for Biff's, mine
is one
character longer than Biff's formula; but, as Sandy pointed out,
Biff
will have to modify his formula to account for the problem Sandy has
pointed out, so we will see how things shake out on the character
count
later.

Rick








  #25   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Days to next anniversary

Hi Ron

Don't know what was going on with my previous replies getting screwed
up.
Yes is does work for UK, provided you switch the month and day around
=IF(A1,LOOKUP(365,((TEXT(A1,"d/m/")&YEAR(TODAY())+{0,1})-TODAY())),"")

--
Regards

Roger Govier


"Ron Coderre" wrote in message
...
Thanks, Roger

and, very true about the date format....But, I had it in the back of
my mind
that the formula could easily be tweaked for non-US date formats.
Hopefully,
that holds true.

***********
Best Regards,
Ron

XL2002, WinXP





  #26   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
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

  #27   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default Days to next anniversary

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

  #28   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default Days to next anniversary

Don't know what was going on with my previous replies getting
screwed up.


Yeah, that was strange.

Yes is does work for UK, provided you switch the month and day around
=IF(A1,LOOKUP(365,((TEXT(A1,"d/m/")&YEAR(TODAY())+{0,1})-TODAY())),"")


Actually, it won't work properly as there is a flaw in it, and in ALL of the
other formulas as well... see either of my two latest postings for the
explanation.

Rick

  #29   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default Days to next anniversary

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


  #30   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
Default Days to next anniversary

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





  #31   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
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
posted<g); 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
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
posted<g); 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
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
posted<g); 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,202
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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to display anniversary date sharita Excel Worksheet Functions 3 February 13th 07 12:57 AM
Calculating Anniversary Penny Excel Discussion (Misc queries) 5 October 7th 05 03:31 PM
recurring anniversary dates Blackhawk Excel Discussion (Misc queries) 0 August 2nd 05 06:21 AM
calculate anniversary of date after specified date slymeat Excel Worksheet Functions 3 July 5th 05 01:53 AM
How do I get an anniversary date? Steve j. Excel Worksheet Functions 1 November 18th 04 05:10 PM


All times are GMT +1. The time now is 04:31 PM.

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

About Us

"It's about Microsoft Excel"