Min and Max Dates in Range that Contains Zeroes, Dates, and Number

[Ezra]

I just can't figure this one out. In Row 7, have a range from Column C to
Column BE that contains cells containing numbers, cells containing dates, and
cells containing text. There may be zeroes, the date equivalent of zero, or
blank cells in each. Is there a formula that will return the value of the
minimum date in the range, disregarding the "zeroes" in date cells and
disregarding the cells containing numbers and text?

Many thanks in advance for any suggestions!

 

[Jacob Skaria]

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=MIN(IF(ISNUMBER(C7:BE7),IF(YEAR(C7:BE7)>1900,C7:BE7)))

If this post helps click Yes

 

[T. Valko]

It depends on what the numbers are and what the dates are.

Since dates are really just numbers formatted to look like dates this can be
a problem.

If the numbers are within a certain range, say, 0 to a max of 100, and the
dates are all greater than a certain date then you can try an array formula
like this:

=MAX(IF(C7:BE7>100,C7:BE7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Ezra]

Jacob:

Worked like a charm! I've tested a few combos of entries in different cells,
and so far, it seems to return the correct result each time. Thank you so
much! I must admit that I always have trouble wrapping my brain around array
formulas, and I doubt I would have come up with this EVER. Thanks!

 

[Ezra]

So far, Jacob's idea just above seems to be working OK, but if I start
getting bogus results, I' ll keep this on tap, too. Thanks!

 

[T. Valko]

Here's how it works...

In Excel dates are stored as the sequential count of days starting from a
base date. That base date is 1/1/1900. The numeric value of 1/1/1900 is 1.
1/2/1900 = 2, 1/3/1900 = 3, 1/4/1900 = 4, etc. Today is 8/25/2009. The
numeric value of 8/25/2009 is 40050. It's the 40,050th day since the base
date of 1/1/1900.

When you enter a date Excel (usually) automatically formats the cell to look
like a date. For example, if you type in 8/25/2009 Excel *displays* that as
the date 8/25/2009 but the true value of that cell is 40050.

You can see this numeric value by entering any date in a cell then change
the format of that cell to General.

So, you can probably see how this makes differentiating dates from numbers
kind of tricky.