| 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
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 |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
|
#9
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 |
|
#10
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 |
|
#11
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 |
|
#12
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 |
|
#13
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#15
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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#19
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 |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| 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 | |||
Linear Mode
