Max date per month

I get multiple invoices, each containing a column of service dates. These can come in with some weirdly formatted dates, which I have to convert to a TxServDt column. My formula of choice (works best on most invoices) is this:

Please Login or Register  to view this content.

Now there's a new wrinkle: instead of the equivalent date of what the vendor supplies, I instead need the last date in the month (not "last day of the month", but "last date in the column for the month in question", so max date per month). See the example attached. Column A is what the vendor provided, column B is what my formula supplies, column C is the result I need. Note that we have May, June and July dates, so what I need is (in this example), 5/30, 6/30, and 7/28.

How, for my TxServDt column, can I both convert the date AND make sure it's the max date for that month?

Also, related, my formulas are delivered through a macro. Each vendor puts their date column in a different area, and calls it something different, and formats it differently. So a formula is best because if my macro chooses the wrong column (there are actually multiple different date columns, so it's a guessing game for my macro) I can re-aim the formula to the right column.

Any help is appreciated.

Please try at B3

=MAX(--SUBSTITUTE(FILTER($A$3:$A$5584,LEFT($A$3:$A$5584,2)=LEFT(A3,2)),"'",))

or a lot faster with helper column

D3
=--(SUBSTITUTE(A3,"'",))

E3
=MAXIFS($D$3:$D$5584,$D$3:$D$5584,"<"&EOMONTH(D3,0)+1)
=MAX(--SUBSTITUTE(FILTER($A$3:$A$5584,LEFT($A$3:$A$5584,2)=LEFT(A3,2)),"'",))

I apologize for taking so long to respond. Bo_Ry and Josephteh, both of your formulas worked on my sample data, but as I said the different invoices come in with weirdly formatted dates. See the new workbook attached. Lines 3 to 10 have the dates supplied with my newest invoice. So my formula would have to work with these, too, which yours don't seem to.

Okay, I've got something that will work. Basically, I'm using two columns "Orig TxSvcDate" and TxSvcDate". The first has my original formula, the second has a Max(Index formula that looks at the previous column.

Bo_Ry, I would have used your MAXIFS formula instead, but one of my coworkers STILL hasn't updated their version of Excel, so MAXIFS won't work.

See updated workbook.