PC Review


Reply
Thread Tools Rate Thread

A better way to get Min and Max date

 
 
Rob
Guest
Posts: n/a
 
      9th Mar 2008
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 With Quote
 
 
 
 
Jon Peltier
Guest
Posts: n/a
 
      9th Mar 2008
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 With Quote
 
Barb Reinhardt
Guest
Posts: n/a
 
      9th Mar 2008
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
>
>
>

 
Reply With Quote
 
Rick Rothstein \(MVP - VB\)
Guest
Posts: n/a
 
      9th Mar 2008
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" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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 With Quote
 
Rob
Guest
Posts: n/a
 
      9th Mar 2008
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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 With Quote
 
David
Guest
Posts: n/a
 
      10th Mar 2008
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" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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 With Quote
 
Rick Rothstein \(MVP - VB\)
Guest
Posts: n/a
 
      10th Mar 2008
Unclear, confusing, obscure... basically, as used with coding, hard to read
or decipher.

Rick


"David" <(E-Mail Removed)> wrote in message
news:372F4902-BD7C-4081-8002-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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 With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      10th Mar 2008
You mean, SOP.

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


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Unclear, confusing, obscure... basically, as used with coding, hard to
> read or decipher.
>
> Rick
>
>
> "David" <(E-Mail Removed)> wrote in message
> news:372F4902-BD7C-4081-8002-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in message
>>> > news:(E-Mail Removed)...
>>> >> 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 With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      10th Mar 2008
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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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 With Quote
 
Peter T
Guest
Posts: n/a
 
      10th Mar 2008
> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
what method to use to add 1 day to a date (hotel reservation for one night by clicking on calender) and get it displayed in date formate in a textbox for departure date? Amanda Microsoft VB .NET 2 10th Oct 2006 06:52 AM
Link date to a table with Date Range (Eff Date and term date) =?Utf-8?B?S2V2aW4gUmVlZA==?= Microsoft Access 1 31st Aug 2006 04:23 PM
i have two date fileds Opend date Due date, can i set default on due date so, its always = to open date on my data entry form1 Urgent Mike Saifie Microsoft Access Form Coding 1 9th Mar 2006 01:08 AM
Run Query from date to date, Print the from date to date in the header of the report? Dustin Swartz Microsoft Access Queries 1 25th Jan 2005 07:06 PM
Date fields automatically changing when today date matches date field entry.Help! Brian Cassin Microsoft Access Forms 1 15th Nov 2003 01:51 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:20 AM.