Hi Rick

Your first formula doesn't work in my environment (Excel 2003, Xp SP2).
Escape sequence seems not to work for me.
Am i doing somthing wrong?
and i wonder how can i apply this method to format like below.
Dates is surrounded by quotes

"Dates:"2008 Feb 08 to 2008 Mar 02

i can't find the way to make this.

keiji

"Rick Rothstein (MVP - VB)" wrote in
message ...
You could have further obfuscated the statement by
hiding "Dates: " in the first format....

I didn't want to confuse the reader by doing that. <g

But, as long as you insist <bg, it could be done either this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""\Dat\e\s: dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

or this way...

ActiveCell.FormulaR1C1 = _
"=TEXT(MIN(R[3]C:R[65000]C),""""""Dates: """"dd mmm yyyy"")&" & _
"TEXT(MAX(R[3]C:R[65000]C),"" to dd mmm yyyy"")"

Yes, either one of these is much better. <vbg

Rick