Days to next anniversary

[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

 

[Dave O]

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.

 

[Sandy Mann]

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())

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

 

[T. Valko]

Try this:

A2 = hire date

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

 

[Sandy Mann]

A2 = hire date

=IF(OR(A2="",A2>TODAY()),"",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

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

 

[Guest]

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

Stefano

 

[T. Valko]

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.

 

[Sandy Mann]

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

 

[Rick Rothstein \(MVP - VB\)]

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(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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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")

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

Rick

 

[T. Valko]

Ah, but *I* will! <argh!>

<BG>

 

[Nostalgie]

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

 

[T. Valko]

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()))

 

[T. Valko]

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()

 

[Guest]

....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

 

[Guest]

...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

 

[T. Valko]

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.

 

[T. Valko]

Argh!

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

See Ron's beauty!

 

[Roger Govier]

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>> > >>> > >>> >>> >>> >