Google Sheets: Lot Number to Date...?

I have lot numbers in the following format:
EC06JUN2024
ET02APR2024
ER05MAY2024

Is there anyway I could make a formula that would convert them to this:
6/30/2024
4/30/2024
5/31/2024

-truncate/discard the first 4 characters
-convert the month abbreviation to a number
-and here's the really tricky part, make it the last day in that month (or at least the 29th)

So far I have come up with =LEFT("")&MID(A1,5,3)&"/29/"&RIGHT(A1,4)
which gives me
JUN/30/2024
APR/30/2024
MAY/31/2024

Which if typed in would convert with formatting...

Any thoughts anyone has would be greatly appreciated.
Nick

Try

=EOMONTH(DATE(--RIGHT(A1,4),MONTH(MID(A1,5,3)&0),1),0)

format as required

1. Your profile says Excel 2003 - is this up-to-date?
2. You never mentioned Google Sheets, so nobody knew that this was meant to be a formula for that platform.
3. You posted in the wrong forum section for a Google Sheets query. I will move the thread now.

Similar to JohnTopley's

Don't know if it works in Google sheets or not. Try

=EOMONTH(RIGHT(A1,7),0)

and fill down.

EOMONTH automatically converts the date strings to numbers.

the formula by FlameRetired works well in Google Sheets

Glad to hear.
Thank you for the feedback and added rep.

If this answers your question please take time to mark your thread Solved.