End of Month Function

End of Month Function

Good afternoon all,

I'm trying to figure out how to apply the =EOMONTH(A1,0) from excel into a smartsheet.

Any guidance on this would be most appreciated!

Thanks!

Best Answer

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Smartsheet does not currently have this function. I would suggest that you Submit a Product Enhancement Request when you have a moment.


    In the meantime, there is a solution. Do you need to be able to add n months before calculating the end of the month, or will you just need the end of a specified month without any time added?

  • Hi Paul,

    Thanks for the response! I'll absolutely put in that Enhancement Request

    In the industry that I'm employed, we have training & checking events that are regulated by the government. Regardless of when the event is completed, they are always due on the last day of the month. Due to the COVID-19 pandemic, the are granting a 90 day grace period from when the event was actually due. As with the current rule, it is not a hard 90 days, but rounds to the end of the month in question. So for example:

    Event Completed 3/22/2019

    Event Due 3/31/2020

    Event Due plus 90 grace 6/29/2020

    Actual Due date including the grace period: 6/30/2020

    In the Screen Shot above, i'm using the following formula to add the 90 days ( =Base1 + [Grace Days Allowed]1 ), but in order to prevent confusion with some of the line level employes who are not as well versed in the regulations, i really need to find a way to round to month end.

    thanks again for all of your help!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. So if you are already adding in your 90 days and have that set as a date in your sheet, that makes things much simpler. I am assuming you want the end of the month for the date in the [90 Day Grace Expires] column.


    To do this we need to actually calculate the first of the following month and subtract 1 from it. TO do this, we actually need to use two different formulas. One that will simply add 1 to the month number and another for any December dates that will add 1 to the year and change the month number to 1. That is because there is no month 13, so month 12 + 1 will throw an error.


    So to add 1 month:

    =DATE(YEAR([90 Day Grace Expires]@row), MONTH([90 Day Grace Expires]@row) + 1, 1)


    To account for December dates you would use:

    =DATE(YEAR([90 Day Grace Expires]@row) + 1, 1, 1)


    We can use an IFERROR to combine the two so that the first will run first and if it throws an error for December dates, it will run the second.

    =IFERROR(DATE(YEAR([90 Day Grace Expires]@row), MONTH([90 Day Grace Expires]@row) + 1, 1), DATE(YEAR([90 Day Grace Expires]@row) + 1, 1, 1))


    Then when we subtract a day from the first of the next month, we get the last day of the referenced month:

    =IFERROR(DATE(YEAR([90 Day Grace Expires]@row), MONTH([90 Day Grace Expires]@row) + 1, 1), DATE(YEAR([90 Day Grace Expires]@row) + 1, 1, 1)) - 1


    And there you have it. The last day of the month based on a specific date.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Additional Note:

    Sorry for the rambling in that last post. I can get a little carried away when it comes to automating things. Haha

  • Paul, I can't tell you how much i appreciate your help with this!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

Sign In or Register to comment.