Still messed up, even though clear on my screen before posting. Last
attempt.

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 placeof the double "dd "and double "mm".--RegardsRoger Govier"Roger Govier" wrote in l... Not sure what happened with that posting???. Hope this comes outclearer. Hi Ron Very nice solution. Just need to remember that the whole world doesn't use US dateformats!<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 toBiff'sNOW() in place of TODAY() you can trim another 2 charactersbyusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Roger Govier" wrote in l... HiRon Very nice solution. Just need to remember that the whole worlddoesn't use US dateformats!<vbg. It gives some strange results in theUK unless you modifyto=IF(A1,LOOKUP(365,((TEXT(A1,"dd/mm/")&YEAR(TODAY())+{0,1})-TODAY())),"")And if you are after reducing formula length, then in additiontoBiff'sNOW() in place of TODAY() you can trim another 2 charactersbyusing"m/d/" or "d/m/" in place of the double dd anddoublemm--RegardsRoger t.com text and future values wouldn't be entered asastartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP"RonCoderre" wrote: ...or even afewmore...=IF(A1<"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a startdateof02/29/2000...but, so far so good.) ***********Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trimsome morecharacters: 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 bl... Charactercount?Where's your error checking? <g I was trying to comeupwith a shortcut for: +(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY() Butthisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and Istillretain 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 someerrorchecking: =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 . .. I want toshow thenumber of days between the current date(today) and thehiringdate in order to provide an indication of howmany days istheanniversary away. Any suggestions? Here is myoffering(assumes "hire date" is in A1)... =DATEDIF(TODAY(),DATE(YEAR(TODAY())+(DATE(Y EAR(TODAY()),MONTH(A1),DAY(A1))<TODAY()),MONTH(A1) ,DAY(A1)),"d") Whilecharactercount is not an absolute metric, I note thatthis formula is theshortest one offered so far except for Biff's,mine isone character longer than Biff's formula; but, as Sandy pointedout,Biff will have to modify his formula to account for theproblem Sandyhas pointed out, so we will see how things shake outon thecharactercount later. Rick