Finding min and max date of a given month

[Caro-Kann Defence]

Hi.

I seem to have one side of the following problem (ie. finding the last date).

I have a list of historical stock quotes and want to find the opening and
closing dates for each month. I can find the last date recorded for a month
(where I use YYYY-MM as the format) using the formula

=MAX((TEXT(Sheet1!$A$2:$A$50000,"yyyy-mm")=$B3)*(Sheet1!$A$2:$A$50000))

However, when I try to change the MAX to MIN it doesn't seem to work.

Any help would be appreciated.

Thanks!

 

[Bernie Deitrick]

Caro-Kann Defence,

=MIN(IF(TEXT(Sheet1!$A$2:$A$50000,"yyyy-mm")=$B3,Sheet1!$A$2:$A$50000))

HTH,
Bernie
MS Excel MVP

 

[Caro-Kann Defence]

You're the best!

Caro-Kann Defence,

=MIN(IF(TEXT(Sheet1!$A$2:$A$50000,"yyyy-mm")=$B3,Sheet1!$A$2:$A$50000))

HTH,
Bernie
MS Excel MVP