EOMONTH Formula

You can do this by creating a helper sheet to reference the last day of each month, and look that up from within your formula.

Create two columns, MonthNum and LastDay, with LastDay being a date-type column. Populate with the month number in the MonthNum column, and a formula for creating the date of the last day of the following month in the LastDay column. (This formula will work for every year except for those pesky leap years! For those you'll have to change January's formula from 28 to 29, or just don't worry about it!)

MonthNum LastDay

1 _______ =DATE(YEAR(TODAY()), 2, 28) which equals 28/2/22

2 _______ =DATE(YEAR(TODAY()), 3, 31) which equals 31/3/22

3 _______ =DATE(YEAR(TODAY()), 4, 30) which equals 30/4/22

... and so on to December:

12 _______ =DATE(YEAR(TODAY()) + 1, 1, 31) which equals 31/1/23

So now your helper sheet shows the last day of the following month for each current month.

Now use an INDEX/MATCH to find the last day based on the current month:

=IFERROR(INDEX({Helper Sheet LastDay column ref}, MATCH(MONTH([Invoice Date]@row), {Helper Sheet MonthNum column ref}, 0)), "")

Use the "Reference Another Sheet" link when typing your INDEX/MATCH, in order to reference the columns in your helper sheet. A simple copy/paste of my formula will not work, you need to type it out in order to be able to create the column references!

Isn't that what we just did?

Your helper sheet should look like this, with 1 corresponding to the last day in February, 2 corresponding to the last day in March, etc:

For Invoice Dates in January, you want to find the last day in February, for Invoice dates in February, find the last day in March, yes? That's what this formula does, provided your helper sheet is setup as above.

=IFERROR(INDEX({Helper Sheet LastDay column ref}, MATCH(MONTH([Invoice Date]@row), {Helper Sheet MonthNum column ref}, 0)), "")

In English, return the value from the LastDay column where the MonthNum equals the current month.