# 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!

Thanks,

Brittney

Tags:

• ✭✭✭✭

Hi @bkelley ,

I'm not sure how you're doing the Quarterly, Annually rows etc. but from what you described I think you could simplify things a bit I think.

Current Month (Hidden) : =Today()

Period End Date: =IFERROR(DATE(YEAR([Current Month]@row), MONTH([Current Month]@row) + 1, 1), DATE(YEAR([Current Month]@row) + 1, 1, 1)) - 1

(Courtesy of Paul Newcome from here: https://community.smartsheet.com/discussion/77535/how-to-create-a-formula-to-show-last-day-of-the-month)

Due Date: =[Period End Date]@row + [Deadline in days after period end (HIDDEN)]@row

• ✭✭✭✭

Hi @bkelley ,

I'm not sure how you're doing the Quarterly, Annually rows etc. but from what you described I think you could simplify things a bit I think.

Current Month (Hidden) : =Today()

Period End Date: =IFERROR(DATE(YEAR([Current Month]@row), MONTH([Current Month]@row) + 1, 1), DATE(YEAR([Current Month]@row) + 1, 1, 1)) - 1

(Courtesy of Paul Newcome from here: https://community.smartsheet.com/discussion/77535/how-to-create-a-formula-to-show-last-day-of-the-month)

Due Date: =[Period End Date]@row + [Deadline in days after period end (HIDDEN)]@row

• Hi @ericncarr ,

I actually did come across Paul's formula but couldn't get it to work for me. I'm still getting an error even after trying to just start from scratch. Not sure what I'm doing wrong here.

• ✭✭✭✭

@bkelley What's the error message you're getting? Is your "Current Month" column a Date column or a Text column? Make sure all of them are date columns, that ought to do it.

• @ericncarr - sending virtual hugs your way!!!! That fixed it!! Thank you so much!!!!!!

• ✭✭✭✭

@bkelley You're welcome glad that did it!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!