MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Extracting dates from named range

  Asked By: Daisy    Date: Dec 25    Category: MS Office    Views: 2225

i'm having yet another named range issue...

I've a named range that contains a list of dates. What i'd like to
do is extract the earliest and most recent dated from that range.
the following should do it from what i can make out, but for some
reason they both return the same date

a = CDate(Application.WorksheetFunction.Max(Range("myDATES")))
MsgBox a
b = CDate(Application.WorksheetFunction.Min(Range("myDATES")))
MsgBox b

If all the dates are the same then a & b are 0, and i can quite
happily deal with that by extracting just the date in the first cell
of the named range, but the dates in this particular column span
about two years, so Max and Min shouldn't have any problems
distinguishing between them.

Any pointers?



6 Answers Found

Answer #1    Answered By: Charlotte Brown     Answered On: Dec 25

I wouldn't have expected that you'd need (or want) a CDate conversion of what
Max returns, as it should already be a date.

What happens if you remove it?

Answer #2    Answered By: Abbie Hughes     Answered On: Dec 25

I added the CDate function so the variable would display as a date
(say, 01/05/2005) in the msgbox. If i remove it they just display
the date  as a number (38473). Either way, the max  and min  is
returning the same value and not distinguising between the dates.

For example, I know that the earliest date in the named  range is
01/05/2004 and the most recent is 03/02/2006. Using the code
originally posted, both Max and Min return  the 01/05/2004 date. If i
remove the CDate they both return 38108.

If i do the same thing on the worksheet rather than use VBA they
behave as expected, which is why i can't figure out why the VBA
fails to tell the difference.

Answer #3    Answered By: Myrtle Wilson     Answered On: Dec 25

Okay, after a bit more tinkering, I think i know what's happening,
but i've no idea why or how to correct it.

Application.WorksheetFunction.Max or Application.WorksheetFunction.
Min seem to be evaluating the entire named  range but are returning
the value of the first cell in the range  not the value of the cell
that corresponds to the actual max  or min. Don't know if this sheds
any light on what's happening.

Answer #4    Answered By: Cain Smith     Answered On: Dec 25

I created a myDATES range  with several thousand dates, and your unmodified code
works perfectly for me. It sounds like Min and Max are only looking at the first
cell in your range. If the name is defined via VBA, maybe it's not including all
the cells you think it is. If this is part of a longer procedure, maybe the name
is being re-assigned to the single cell somewhere, before you are calling Min &
Max. Why not stick a statement like

MsgBox Range("myDATES").Address

just before your Min/Max calls, to see what cells are included in the search

Answer #5    Answered By: Aaeedah Khan     Answered On: Dec 25

Not been following this but does sorting the named  range help?

Answer #6    Answered By: Lucio Ferrrari     Answered On: Dec 25

Sorting the named  range doesn't affect min  & Max, as it might affect a Vlookup.
I sorted the myDATES range  I created in ascending, descending, and random
orders, and got the right answers every time.

Didn't find what you were looking for? Find more on Extracting dates from named range Or get search suggestion and latest updates.