Can I do this with built in functions?

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

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
 
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)
 
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
 
Back
Top