End of Month Function

amiller66941

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!

Paul Newcome

NOTE:

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(Base@row + [Grace Days Allowed]@row), MONTH(Base@row + [Grace Days Allowed]@row) + 1, 1), DATE(YEAR(Base@row + [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.

Paul 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?

amiller66941

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 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 Newcome

NOTE:

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(Base@row + [Grace Days Allowed]@row), MONTH(Base@row + [Grace Days Allowed]@row) + 1, 1), DATE(YEAR(Base@row + [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.

Paul 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

amiller66941

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

Paul Newcome

Happy to help! 👍️

jgutierrez111926

Thanks Paul. took me a little bit to get this working based on the input sheet example and your scripting.

It would be greatly helpful if the functions provided data types. Example. This functions returns data type of date so it must be in a column of data type.

jwg

Paul Newcome

@jgutierrez111926 I'm not sure I follow... Are you asking for clarification on something?

Edgar Meza Cabrera

Hello everyone. I wanted to post this here as this is the first result I get when seraching for EOMONTH SmartSheets.

Here is how you can build the EOMONTH function in SmartSheets using the DATE function along with the YEAR, and MONTH functions. I believe this just a more generalized version of the solution that Paul already posted for this specific question.

The general formula is DATE(YEAR( [Date] ), MONTH( [Date] ) ± [N], 1) - 1. Is would be analogus to the Excel EOMONTH( [Date], [N]) where [Date] = reference date, and [N] = increase (+) or decrease (-) in the number of months. Some examples using OP's format with difference number for [N].

A word of caution, though, this will only work for months that are contain within the same calendar year (date math is always hard) so if that is a concern a likely solution is for this formula to be part of the an IF statement that checks to see if the [Date] month equals 12 (if increasing months) or 1 (decreasing months) and make the true argument of the IF function also increase or decrease (as appropriate) the YEAR argument of the DATE function.

Excel: EOMONTH(A1,0) - SS: DATE(YEAR( A1 ), MONTH( A1 ) + 0, 1) - 1 {of course 0 is redundant but showing for formula clarity}

Excel: EOMONTH(A1,3) - SS: DATE(YEAR( A1 ), MONTH( A1 ) + 3, 1) - 1

Excel: EOMONTH(A1,-2) - SS: DATE(YEAR( A1 ), MONTH( A1 ) -2, 1) - 1

Paul Newcome

@Edgar Meza Cabrera That is actually PART of my solution.

=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

The IFERROR is in place for when we are in December since there is no month number 13.

If you are wanting to look forward by multiple months, it would be the same syntax with the IFERROR but with a slightly different MONTH portion in the second DATE. The below example is for going out 3 months.

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

Basically the first DATE function adds the number of months to the month. The second DATE function adds one to the year and then subtracts whatever number would be added to the first to equal 12.

There is also another solution out there already (I would have to dig for it, but I know it is there) that allows you to put a variable number in a separate column to indicate how many months into the future you want to go and it will automatically calculate both the appropriate year and month.

CamSME

@Paul Newcome and @Edgar Meza Cabrera your tips really helped me today. I wanted to share the formula I've created for EOMONTH when using variables that potentially exceed one year.

I added columns for yearly and monthly intervals, with the month always ending on the 30th (it's close enough), then I added an IFERROR that will subtract 3 days in case an error populates for February. So far this is correctly calculating the subsequent Due Date from the date of Last Completion. Some of our periodicity is for every 15 or 18 months. In this case, I add 1 year and 3 months or 1 year and 6 months.

=IFERROR(DATE(YEAR([Last Completed]@row) + ([Yearly Interval]@row), MONTH([Last Completed]@row) + ([Monthly Interval]@row), 30), DATE(YEAR([Last Completed]@row) + ([Yearly Interval]@row), MONTH([Last Completed]@row) + ([Monthly Interval]@row), 1)) - 3

Paul Newcome

@CamSME It sounds like you are getting more into the EDATE realm. Here is a formula that would generate a date based on any number of months (positive or negative) and has been tested on dates for 10 years in both directions without issue.

It does currently pull the DAY, but you can replace DAY([Original Date]@row) with 1 to get the first of the month and then subtract 1 from the date output to get the EOM date.

=IFERROR(DATE(YEAR([Original Date]@row) + ROUNDDOWN((MONTH([Original Date]@row) + [Number Of Months]@row) / 12, 0) + IF(IF(MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number Of Months]@row) - MONTH([Original Date]@row) <> 12, [Number Of Months]@row < 0, ABS([Number Of Months]@row) > MONTH([Original Date]@row)), 1, 0), IF(MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [Number Of Months]@row, 12)), 1), DATE(IF(MONTH([Original Date]@row) - ABS([Number Of Months]@row) < 1, YEAR([Original Date]@row) - 1, YEAR([Original Date]@row)), IF(MONTH([Original Date]@row) - ABS([Number Of Months]@row) < 1, MONTH([Original Date]@row) + (12 - ABS([Number Of Months]@row)), MONTH([Original Date]@row) - ABS([Number Of Months]@row)), DAY([Original Date]@row)))

CamSME

@Paul Newcome Thank you for this. You've saved me again with a great formula! What I was using wouldn't work beyond the subsequent year.

Paul Newcome

@CamSME Sure thing! One thing to keep in mind in all EOM calcs... It is much easier to just add an extra month, make it the first of the output month, then subtract 1 day than it is to try to accommodate how many days are going to be in the output month.

So instead of adding 15 or 18 months, add 16 or 19 months, use the number 1 in the day portion of the DATE function, then subtract 1 from it to get the last day of the 15th or 18th month.

Using my example from 26 August '21:

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

The above outputs the first of the month, 3 months after the [90 Day Grace Expires] date. If we wanted the EOM for three months out, we would go to the first of 4 months out:

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

Then subtract one day:

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