Can I do this with built in functions?
 

Hello all,

I have a list of wordday dates over 5 years (approx 1200 rows). The
last day of the month will not always be listed. I want to return the
max date that is less than the last day of the month. Can this be
done with the built in functions of Excel?

I was thinking:
=MAX(B5:B120)<A1
But that is comparing the values returning T/F.

Thanks for any help anyone can provide,

Conan Kelly

Can I do this with built in functions?
 

Try

=MAX(IF(B5:B120<A1,B5:B120))

as an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Conan Kelly" <CTBarbarin at msn dot com wrote in message
...
Hello all,

I have a list of wordday dates over 5 years (approx 1200 rows). The
last day of the month will not always be listed. I want to return the
max date that is less than the last day of the month. Can this be
done with the built in functions of Excel?

I was thinking:
=MAX(B5:B120)<A1
But that is comparing the values returning T/F.

Thanks for any help anyone can provide,

Conan Kelly

Can I do this with built in functions?
 

See if this gives you something to work with:

A1: (any date)
A2: =A1-DAY(A1)-WEEKDAY(A1-DAY(A1),1)-1

Returns the last Friday of the month prior to the date in A1.

Does that help?

***********
Regards,
Ron


"Conan Kelly" wrote:

Hello all,

I have a list of wordday dates over 5 years (approx 1200 rows). The
last day of the month will not always be listed. I want to return the
max date that is less than the last day of the month. Can this be
done with the built in functions of Excel?

I was thinking:
=MAX(B5:B120)<A1
But that is comparing the values returning T/F.

Thanks for any help anyone can provide,

Conan Kelly

Can I do this with built in functions?
 

Bob,

That worked great. Thanks for the help.

Conan


"Bob Phillips" wrote in message
...
Try

=MAX(IF(B5:B120<A1,B5:B120))

as an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Conan Kelly" <CTBarbarin at msn dot com wrote in message
...
Hello all,

I have a list of wordday dates over 5 years (approx 1200 rows).
The
last day of the month will not always be listed. I want to return
the
max date that is less than the last day of the month. Can this be
done with the built in functions of Excel?

I was thinking:
=MAX(B5:B120)<A1
But that is comparing the values returning T/F.

Thanks for any help anyone can provide,

Conan Kelly