| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenate including a date so that the date appears as a date | Excel Worksheet Functions | |||
| date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| copy date based on date -refer to date range | Excel Programming | |||
| Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Hybrid Mode
