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

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

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

"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

Ah, but *I* will! <argh!

<BG

--
Biff
Microsoft Excel MVP


"Sandy Mann" wrote in message
...
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

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

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

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

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

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

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

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

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