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
  #11  
Old July 8th, 2007, 08:56 PM posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\)
external usenet poster
 
Posts: 1,319
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  
Old July 8th, 2007, 10:32 PM posted to microsoft.public.excel.misc
Nostalgie
external usenet poster
 
Posts: 1
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  
Old July 8th, 2007, 10:39 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
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  
Old July 8th, 2007, 10:56 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
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  
Old July 9th, 2007, 12:16 AM posted to microsoft.public.excel.misc
Ron Coderre
external usenet poster
 
Posts: 620
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  
Old July 9th, 2007, 12:32 AM posted to microsoft.public.excel.misc
Ron Coderre
external usenet poster
 
Posts: 620
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  
Old July 9th, 2007, 02:44 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
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  
Old July 9th, 2007, 02:48 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
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  
Old July 9th, 2007, 10:13 AM posted to microsoft.public.excel.misc
Roger Govier
external usenet poster
 
Posts: 2,602
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 titleg: =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

 




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 02:51 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.