Days to next anniversary

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
This worked for me: with the anniversary date in cell A1,
=IF(MONTH(TODAY())<=MONTH(A1),DATE(YEAR(TODAY()),MONTH(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.
 
Dave O said:
This worked for me: with the anniversary date in cell A1,
=IF(MONTH(TODAY())<=MONTH(A1),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-
TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY())
Click to expand...
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(YEAR(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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Try this:

A2 = hire date

=IF(OR(A2="",A2>TODAY()),"",ABS(DATE(YEAR(TODAY())+(MONTH(TODAY())>MONTH(A2)),MONTH(A2),DAY(A2))-TODAY()))
 
T. Valko said:
A2 = hire date

=IF(OR(A2="",A2>TODAY()),"",ABS(DATE(YEAR(TODAY())+(MONTH(TODAY())>MONTH(A2)),MONTH(A2),DAY(A2))-TODAY()))
Click to expand...
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy Mann said:
Returns 358 for date in A2 of June 30 2007 and 7 to 1 for July 1 2007 to
July 7 2007 respectively.
Click to expand...

Ooops! Back to the drawing board!
Why return an empty string for dates in the future?
Click to expand...

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.
 
It's alright Biff, it's Sunday - no one will notice <g>

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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?
Click to expand...

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 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
 
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
 
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")
Click to expand...

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

Rick
 
Bonsour® Rick Rothstein (MVP - VB) avec ferveur ;o))) vous nous disiez :
May be this formula is shortest ??? :
=DATE( YEAR(TODAY()) + (DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()) ,
MONTH(A1) , DAY(A1)) - TODAY()

HTH
 
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="",A1>TODAY()),"",ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))
 
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()
 
....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
 
...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
 
We can trim a couple more:

=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.
 
Argh!

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

See Ron's beauty!
 
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" <[email protected]> wrote in message..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" <[email protected]> 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="",A1>TODAY()),"",ABS(DATE(YEAR(NOW())+(DATEVALUE(MONTH(A1)&"/"&DAY(A1))<TODAY()),MONTH(A1),DAY(A1))-TODAY()))>> > >>> > > -->> > > Biff>> > > Microsoft Excel MVP>> > >>> > >>> > > "Rick Rothstein (MVP - VB)" <[email protected]>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(TODAY()),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>> > >>> > >>> >>> >>> >
 
Back
Top