A better way to get Min and Max date
 

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

A better way to get Min and Max date
 

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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

Alternatively, you could define a dynamic range and use that in the formulas.

http://peltiertech.com/Excel/Charts/...umnChart1.html
--
HTH,
Barb Reinhardt



"Rob" wrote:

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

A better way to get Min and Max date
 

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


"Jon Peltier" wrote in message ...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

Thanks Jon, just the job. I'll look at the other suggestion of a dynamic
range, see if I can figure it out!

Regards, Rob

"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

What is obfuscated? I am just a country boy.

"Rick Rothstein (MVP - VB)" wrote:

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


"Jon Peltier" wrote in message ...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

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

Rick


"David" wrote in message
...
What is obfuscated? I am just a country boy.

"Rick Rothstein (MVP - VB)" wrote:

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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

You mean, SOP.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


"David" wrote in message
...
What is obfuscated? I am just a country boy.

"Rick Rothstein (MVP - VB)" wrote:

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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

Rick -

I often bury these in a cell's custom number format, but I am reluctant to
do so in a TEXT function or Format (in VBA). I have to understand this when
I review it in six months, and hiding something in a format makes it harder
to see. You could have further obfuscated the statement by hiding "Dates: "
in the first format, and you'd be more confused upon later review.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

You could have further obfuscated the statement by hiding "Dates: "
in the first format

The D, e & s in "Dates" belong to Rick's metacharacters so it would indeed
obfuscate. <g

Not quite sure what the 'e' does though.

Regards,
Peter T

"Jon Peltier" wrote in message
...
Rick -

I often bury these in a cell's custom number format, but I am reluctant to
do so in a TEXT function or Format (in VBA). I have to understand this
when
I review it in six months, and hiding something in a format makes it
harder
to see. You could have further obfuscated the statement by hiding "Dates:
"
in the first format, and you'd be more confused upon later review.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

I'm not sure if this would match your case, but try this one.
assuming your data is continuously filled in Range.

Sub mytest()
Dim startrng As Range, endrng As Range
Dim rngstr As String

Set startrng = ActiveCell.Offset(3, 0)
Set endrng = startrng.End(xlDown)
rngstr = Range(startrng, endrng).Address(False, False, xlR1C1, , ActiveCell)
ActiveCell.FormulaR1C1 = "=""Dates: "" & " & _
"Text(Min(" & rngstr & "), ""dd mmm yyyy"")" & _
" & "" to ""& " & _
"Text(Max(" & rngstr & "),""dd mmm yyyy"")"
End Sub

keiji

"Rob" wrote in message
...
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

A better way to get Min and Max date
 

Well, you'd have to bury them within more quotes. The "e" could be reserved
for scientific notation.

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


"Peter T" <peter_t@discussions wrote in message
...
You could have further obfuscated the statement by hiding "Dates: "
in the first format

The D, e & s in "Dates" belong to Rick's metacharacters so it would indeed
obfuscate. <g

Not quite sure what the 'e' does though.

Regards,
Peter T

"Jon Peltier" wrote in message
...
Rick -

I often bury these in a cell's custom number format, but I am reluctant
to
do so in a TEXT function or Format (in VBA). I have to understand this
when
I review it in six months, and hiding something in a format makes it
harder
to see. You could have further obfuscated the statement by hiding "Dates:
"
in the first format, and you'd be more confused upon later review.

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

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

A better way to get Min and Max date
 

The "e" could be reserved for scientific notation.

- Jon

Yes that it, but needs it a little more to work; from Help

"Scientific notation To display numbers in scientific format, use "E-,"
"E+," "e-," or "e+" exponent codes in a section. If a format contains a 0
(zero) or # (number sign) to the right of an exponent code, Excel displays
the number in scientific format and inserts an "E" or "e". The number of 0's
or #'s to the right of a code determines the number of digits in the
exponent. E- or e- places a minus sign by negative exponents. E+ or e+
places a minus sign by negative exponents and a plus sign by positive
exponents."

=TEXT(1000000,"#e-#")
1e6

Regards,
Peter T

A better way to get Min and Max date
 

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

A better way to get Min and Max date
 

I'm using XL2003 also, but on Vista... both posted formulas work fine for me
(I had tested them before posting and I just re-tested them to make sure).
I'm not sure what difference XP over Vista could possibly make. Anyone else
out there want to try them out? Just put some dates in A3 downward (not
important how many), then make A1 the active cell and run the line of code
(you can so in the Immediate window if you want).

Rick


"kounoike" wrote in message
...
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

A better way to get Min and Max date
 

for more details, This is what i got.

Using your first formula:
\1at\20\0: 01 Feb 2008 to 03 May 2008

Using your second formula is:
Dates: 01 Feb 2008 to 03 May 2008

keiji

"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm using XL2003 also, but on Vista... both posted formulas work fine for
me (I had tested them before posting and I just re-tested them to make
sure). I'm not sure what difference XP over Vista could possibly make.
Anyone else out there want to try them out? Just put some dates in A3
downward (not important how many), then make A1 the active cell and run
the line of code (you can so in the Immediate window if you want).

Rick


"kounoike" wrote in message
...
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

A better way to get Min and Max date
 

Well, yes, for the code I tend to write at least. <g

Rick


"Jon Peltier" wrote in message
...
You mean, SOP.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


"David" wrote in message
...
What is obfuscated? I am just a country boy.

"Rick Rothstein (MVP - VB)" wrote:

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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

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)


"Rick Rothstein (MVP - VB)" wrote in
message ...
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


"David" wrote in message
...
What is obfuscated? I am just a country boy.

"Rick Rothstein (MVP - VB)" wrote:

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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

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

Rick


"Ron Coderre" wrote in message
...
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)


"Rick Rothstein (MVP - VB)" wrote in
message ...
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


"David" wrote in message
...
What is obfuscated? I am just a country boy.

"Rick Rothstein (MVP - VB)" wrote:

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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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

A better way to get Min and Max date
 

Huh?

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
But isn't the point of using the word "obfuscate" to obfuscate what you
are saying?<g

Rick


"Ron Coderre" wrote in message
...
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)


"Rick Rothstein (MVP - VB)" wrote
in message ...
Unclear, confusing, obscure... basically, as used with coding, hard to
read or decipher.

Rick


"David" wrote in message
...
What is obfuscated? I am just a country boy.

"Rick Rothstein (MVP - VB)" wrote:

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


"Jon Peltier" wrote in message
...
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
_______


"Rob" wrote in message
...
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