Finding min and max date of a given month
 

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!

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" wrote in
message ...
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!

You're the best!

"Bernie Deitrick" wrote:

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" wrote in
message ...
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!