If it is always 2 / and you always want to find the position of the last
then you can substitute it with something that can't be in the formula like

=FIND("^^",SUBSTITUTE(J4,"/","^^",2))

will always find the position of the last / if there are 2 of them

with regards to your question yes you can name a part of a formula and use
that name, insertnamedefine
that will shorten your formula quite a bit

example, assume using the above formula and name the substitute part "subst"
f, do insertnamedefine and
put subst in the name and in the reference box put

=SUBSTITUTE(Sheet1!$J$4,"/","^^",2)

now you can use

=FIND("^^",subst)


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




wrote in message
ups.com...
For dates that do not have a year, I will either use 4 blanks or 4
dashed.

here is what I came up with: ( I did not original include the case of
a blank cell)
=IF(<Cell="","",IF(ISTEXT(<Cell),IF(LEN(<Cell) 6,RIGHT(<Cell,4),"----")&"/"&LEFT(<Cell,SEARCH("/",<Cell,1)-1)&"/"&MID(<Cell,SEARCH("/",<Cell,1)+1,SEARCH("/",<Cell,SEARCH("/",<Cell,1)+1)-SEARCH("/",<Cell,1)-1),TEXT(<Cell,"yyyy/mm/dd")))

It is rather long but does work. In situations like this where a
sub-value is calculated often, is there a way to reference it rather
then calculate it each time? ie SEARCH("/",<Cell,1) in the above.

Thanks.