Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
# of Functions per cell | Excel Worksheet Functions |