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

bkelley

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

ericncarr

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

ericncarr

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

bkelley

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.

ericncarr

@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.

bkelley

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

ericncarr

@bkelley You're welcome glad that did it!