EOMONTH Formula

Chloe MB

Hey everyone,

I am trying to add a formula to my pay run sheet. I have tried

=IFERROR(DATE(YEAR([Invoice Date]@row), MONTH([Invoice Date]@row) + 1, 1), DATE(YEAR([Invoice Date]@row, 12, 31))) + 30

which works, however, I need the month-end date and not +30 days as not every month has 30 days.

I need my payment date to read - 31/03/22 if the invoice date was 16/02/22

Invoice Date - 16/02/22

Month End Date - 28/02/22

Following Month, Month End Pay Date - 31/03/22

Any thoughts & help on this would be greatly appreciated? 😊

Jeff Reisman

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!

Chloe MB

https://community.smartsheet.com/discussion/comment/319798#Comment_319798

Thank you so much! It's working and showing the last day of the month based on the invoice date.

Now I need it to then allocate the following months end date for the pay date.

For example 'test 3' invoice date is 11/01/22, so month-end is 31/01/22 which is showing, however, I want the 'Pay Date' to then show the following month's end date (28/02/22). Can this be done?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/319924#Comment_319924

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.

Chloe MB

@Jeff Reisman Yes, I see now. Thank you for your help, it's much appreciated :)

Have a lovely day Jeff!