A better way to get Min and Max date

[Jon Peltier]

Huh?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

But isn't the point of using the word "obfuscate" to obfuscate what you
are saying?<g>

Rick

It's usually best to avoid long words when a diminutive word will
suffice. <g>
(sorry....couldn't resist)
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


What is obfuscated? I am just a country boy.

:

Since the letters "to" are not metacharacters to the TEXT function,
and at the risk of making your statement just a little bit more
obfuscated, you can shorten it a tad more...

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

Rick


You can shorten it a lot:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


Using Excel 2000. The below lines are on two rows, I'd like to
have as a
few more rows so it's easier to read albeit the _ (space
underscore)
doesn't like breaking the lines. Is this possible?

Also, I don't really like the [65000] in the code but I don't know
any
other way to get the MIN and MAX dates from a range that charges
frequently. The below code places in a file then range values.

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

Thanks, Rob