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