Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|