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

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.

"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

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

"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

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

"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

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


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


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

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

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