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!
If you wanted to drop this into your [90 Days Grace Expires] column instead of yet another date column, you would use the formula the is in the [90 Days Grace Expires] column (without the beginning equals sign) in the formula above wherever you see [90 Days Grace Expires]@row.
=IFERROR(DATE(YEAR([email protected] + [Grace Days Allowed]@row), MONTH([email protected] + [Grace Days Allowed]@row) + 1, 1), DATE(YEAR([email protected] + [Grace Days Allowed]@row) + 1, 1, 1)) - 1
If you plan on using this sheet beyond this COVID-19 mess, you can even automate the [Grace Days Allowed] column to automatically populate 90 if it falls between two certain dates and 0 if it falls outside of those dates. You can hardcode those dates in a "helper" column or (if you have access to it) in two Sheet Summary Fields.
Then you would only need to update those two reference dates and the rest of your sheet will automatically determine if the date qualifies for the 90 days or not and will automatically run your calculations for you.
If you are able to automate that part, then you could even modify the formula to reside in the [Event Due] column and hide all of the calculation columns so that everything happens on the "back-end". This could help keep the sheet clean and provides the ability to maintain it even outside of the COVID-19 Grace Period.
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?
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!
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.
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!
Happy to help! 👍️