Find last day of month depending on criteria

J

Jeff Jensen

Range A1:JA1 has all our work days for the year (holidays and weekends are
excluded).

In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.)
and have a formula in Q32 that looks in A1:JA1 and returns the largest date
for the month I entered in P32.

Thank you,

Jeff
 
L

Lars-Åke Aspelin

Range A1:JA1 has all our work days for the year (holidays and weekends are
excluded).

In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.)
and have a formula in Q32 that looks in A1:JA1 and returns the largest date
for the month I entered in P32.

Thank you,

Jeff


Try this formula in cell Q32:

=MAX((A1:JA1)*(MONTH(A1:JA1)=P32))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke
 
R

Ron Rosenfeld

Range A1:JA1 has all our work days for the year (holidays and weekends are
excluded).

In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.)
and have a formula in Q32 that looks in A1:JA1 and returns the largest date
for the month I entered in P32.

Thank you,

Jeff

q32: =LOOKUP(2,1/(MONTH(A1:JA1)=P32),A1:JA1)

--ron
 
T

T. Valko

Try this...

Assuming the dates are for the year 2010.

=LOOKUP(DATE(2010,P32+1,0),A1:AJ1)

Format as Date
 
J

Jeff Jensen

Thanks T. Valko,
I gave it a try but for some reason this is only working month 1 & 2 but it
doesn't for 3 - 12.
Thanks again,
Jeff
 
J

Jeff Jensen

Thanks Lars,
I gave it a try but I get a #VALUE! error. I did enter it as an array.
Thanks,
Jeff
 
T

T. Valko

Hmmm...

I assume the dates are in ascending order?

Works just fine for me.

Oh, well!
 
L

Lars-Åke Aspelin

Make sure that you don't have any non valid dates, like 2010-01-35 in
the range A1:AJ1

Lars-Åke
 
J

Jeff Jensen

Lars, I see what the problem was - some last cells had "", I changed that to
0 and it works now.

I hoped your way would work because I thought I could just change MAX to MIN
in order to get the smallest date as well. But that didn't work. Do you know
a way to get the smallest date?

Thank you,
Jeff
 
L

Lars-Åke Aspelin

Try this formula for the smallest date:

=MIN((IF(A1:JA1<1,99999,A1:JA1)*(IF(MONTH(A1:JA1)=P32,1,2))))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke
 
J

Jeff Jensen

Thanks Lars,

Works Great!

Thanks again,
Jeff

Lars-Ã…ke Aspelin said:
Try this formula for the smallest date:

=MIN((IF(A1:JA1<1,99999,A1:JA1)*(IF(MONTH(A1:JA1)=P32,1,2))))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Ã…ke




.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top