MAX function

[2077delta]

I have the following list of data:

01/12/02
01/12/02
01/12/02
01/19/02
01/19/02
01/19/02
01/19/02
01/26/02
01/26/02
01/26/02
01/26/02
02/02/02
02/02/02
02/02/02
02/23/02
02/23/02
02/23/02
02/23/02
03/02/02
03/02/02
03/02/02
03/15/02
03/15/02
03/26/02
03/26/02

Assume the range of data is named "wedate" and starts in cell A10. In cell A1 is the date 01/31/02. I have the following formula entered into cell A2 =MAX(MONTH(dtldate)=MONTH(A1)) and enter it as an array. I want the formula to return the maximum date from "wedate" that falls in the month in cell A1. Unfortunately my formula returns -0-. Is there a way to write the formula to return the maximum date in "wedate" that falls in the month in cell A1?

 

[Aladin Akyurek]

If wedate is sorted in ascending order, use:

=INDEX(wedate,MATCH(MONTH(A1)&YEAR(A1),MONTH(wedate)&YEAR(wedate)))

If not, use:

=MAX((MONTH(wedate)=MONTH(A1))*(YEAR(wedate)=YEAR(A1))*wedate)

each to be array-entered.

Aladin

 

[Juan Pablo González]

Something like this ?

{=MAX(IF(MONTH(wedate)=MONTH(A1),wedate))}

Array entered.

 

[Nybbe]

Juian pablo wrote
{=MAX(IF(MONTH(wedate)=MONTH(A1),wedate))}

Should it not be
{=MAX(IF(MONTH(wedate)=MONTH(A1)*wedate))}

 

[Aladin Akyurek]

On 2002-08-20 17:35, Nybbe wrote:
Juian pablo wrote
{=MAX(IF(MONTH(wedate)=MONTH(A1),wedate))}

Should it not be
{=MAX(IF(MONTH(wedate)=MONTH(A1)*wedate))}

No, unless you remove the IF.

BTW, maybe adding YEAR is also needed as I proposed.

 

[Juan Pablo González]

On 2002-08-20 17:38, Aladin Akyurek wrote:

No, unless you remove the IF.

BTW, maybe adding YEAR is also needed as I proposed.

Agree Aladin, you know I don't like working with dates... thanks for pointing that out.