Need help with formula for adding days to last day of the month.

Hi there!

I'm trying to create a formula that calculates a due date based on a certain number of days after the end of the month, quarter, year, etc. I wasn't able to figure out how to show the period end date without manually entering it. So I created a formula that looks at the current month and returns the last day of the month. This should work except for February during a leap year.

Last Day of Current Month (HIDDEN) formula:

=IF(OR([Current Month (HIDDEN)]@row = 1, [Current Month (HIDDEN)]@row = 3, [Current Month (HIDDEN)]@row = 5, [Current Month (HIDDEN)]@row = 7, [Current Month (HIDDEN)]@row = 8, [Current Month (HIDDEN)]@row = 10, [Current Month (HIDDEN)]@row = 12), 31, IF(OR([Current Month (HIDDEN)]@row = 4, [Current Month (HIDDEN)]@row = 6, [Current Month (HIDDEN)]@row = 9, [Current Month (HIDDEN)]@row = 11), 30, 28))

Next I combined this day with the current month and year to make it a date:

Period end date formula:

=MONTH(TODAY(0)) + "-" + [Last Day of Current Month (HIDDEN)]@row + "-" + YEAR(TODAY(0))

Then I'd like to add the number of days from "Deadline in Days after period end (HIDDEN)" to the Period end date to get the Due Date. I'd like to achieve having the period end date and due date automatically calculate each month. Any help would be appreciated!




Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!