Thanks, Roger

and, very true about the date format....But, I had it in the back of my mind
that the formula could easily be tweaked for non-US date formats. Hopefully,
that holds true.

***********
Best Regards,
Ron

XL2002, WinXP


"Roger Govier" wrote:

Not sure what happened with that posting???. Hope this comes out
clearer.

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 byusing"m/d/" or "d/m/" in place of the double dd and double mm--RegardsRoger Govier"Roger Govier" wrote in l... 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 characters byusing"m/d/" or "d/m/" in place of the double dd and doublemm--RegardsRoger Govier"Ron wrote
text and future values wouldn't be entered as astartdate=IF(A1,LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") *********** Regards, Ron XL2002, WinXP "RonCoderre" wrote: ...or even a fewmore...=IF(A1<"",LOOKUP(365,((TEXT(A1,"mm/dd/")&YEAR(TODAY())+{0,1})-TODAY())),"") (I thought it would get tripped up by a start dateof02/29/2000...but, so far so good.) *********** Regards,Ron XL2002, WinXP "T. Valko" wrote: Let's trim some 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 message ... Character count?Where's your error checking? <g I was trying to come upwith a shortcut for:
+(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY() But thisdidn't work (I didn't test it enough): +(MONTH(TODAY())MONTH(A2)) This seems to work and I stillretain 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 errorchecking: =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 to show thenumber of days between the current date(today) and the hiringdate in order to provide an indication of howmany days is theanniversary 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 charactercount is not an absolute metric, I note thatthis formula is theshortest 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 thecharactercount later. Rick