Formula for last working day of every month after a start date

BriannaRocamora

I am looking for a formula for last working day of every month after a start date called Open Enrollment@row85

Kerry St. Thomas

Date intelligence can be a little tough in Smartsheet. I recommend you Google "Calendar table" to get familiar with the concept - in essence, it's a sheet that has the list of dates from 1/1/2023 to 12/31/2025 (or whatever date range you choose), with different dimensions such as the last day of the month in question. This will allow you to scale up and do a direct lookup from today's date (or in your case, Open Enrollment date) to get the end of the month. This will also enable you to do things like factor in company holidays, holidays, and business days (especially if your company is structured around a Su-Th calendar).

This is the formula I worked out for the END OF THE MONTH:
=DAY(IFERROR(DATE(YEAR([Open Enrollment]@row), MONTH([Open Enrollment]@row) + 1, 1), DATE(YEAR([Open Enrollment]@row) + 1, 1, 1)) - 1) - (DAY([Open Enrollment]@row) - 1) - 1

It's difficult to say how to change the formula here without knowing more about the structure of your company, the holidays you observe. But hopefully this will give you a basis from which to work, and a more scalable solution that will make it easier to solve later similar issues that will inevitably arise. Good luck!