Formula to return a status based on due date

Liz Carney

Hello, I am trying to return a status for projects based on due date. I have a date column for Due Date that has some blanks and another column for the Status. In the Status column I have the following formula:

=IF(ISBLANK([Due Date]@row), "", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week"))

This is working but I would like to add a status for "Next Week", "2 Weeks" and "3 Weeks" so I can get a sense of what our month will look like. Also anything older but NOT this week should be "overdue" and everything else should be "future."

I can't seem to get it to work when I add these pieces. Any advice?

Thank you!

Mark Cronk

Hi Liz,

OK, I think I have a solution. It's ugly but should work. The problem is the way Smartsheets counts weeks. On 1/2/21, WEEKNUMBER(Today()) is 53. So, we have to check for week 53 and adjust for future weeks that are greater than 52. It takes a bunch of nesting. Give it a try:

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row < TODAY(), "Overdue", IF(WEEKNUMBER(TODAY()) = 53, IF(WEEKNUMBER([Due Date]@row) = 53, "This Week", IF(WEEKNUMBER([Due Date]@row) = 1, "Next Week", IF(WEEKNUMBER([Due Date]@row) = 2, "2 weeks", IF(WEEKNUMBER([Due Date]@row) = 3, "3 Weeks", "Future")))), IF(OR(WEEKNUMBER(TODAY()) + 1 > 52, WEEKNUMBER(TODAY()) + 2 > 52, WEEKNUMBER(TODAY()) + 3 > 52), IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 51, "Next Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 50, "2 Weeks", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 49, "3 Weeks", "Future"))), IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 1, "Next Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 2, "2 Weeks", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 3, "3 Weeks", "Future")), "error"))))))

Mark

Mark Cronk

Hi @Liz Carney ,

Using weeknumbers works unless the weeknumer + the value is greater than 52. That's why there are a few extra IFs up front. Try:

=IF(ISBLANK([Due Date]@row), "", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(Weeknumber([due date]@row) + 1>52, "Next Week" , IF(weeknumber[due date]@row) + 2 >52, "2 Weeks", IF(weeknumber([due date]@row)+3>52, "3 Weeks", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY())+1, "Next Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY())+2, "2 Weeks", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY())+3, "3 Weeks", IF([Due Date]@row) < TODAY()), "Overdue", "Futute"))))))))

Work?

Mark

Liz Carney

Thank you @Mark Cronk! The blank and "This Week" work. Due Dates last week are returning "Next Week", dates two weeks ago are returning "2 Weeks" and 3 weeks ago get "3 weeks" - what should I do to reverse that so I'm looking in the future instead of the past?

Anything older than three weeks ago and anything in the future is returning an #incorrect argument set error.

Mark Cronk

Sorry about that. Try removing the IF statements to check for end of year and see if you get the right answers:

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row < TODAY(), "Overdue", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY())+1, "Next Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY())+2, "2 Weeks", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY())+3, "3 Weeks", "Futute"))))))

If so, we'll need to revise how we treat the last weeks of the year.

Mark

Liz Carney

Yes now "Overdue" is anything in the past, "This Week" is what is left in this week and everything else is "Future."

Mark Cronk

Hi Liz, Are there any due dates in the next 3 weeks that aren't returning the right response? Working on a end of year fix....

If you have completed projects on your list you probably don't want them to reflect over due. You can fix that by incorporating whatever you're using to record the project as complete into the IF([Due Date]@row < TODAY() statement. You'd change that to something like: IF(AND([Due Date]@row < TODAY(), ISBLANK([Date Complete]),

Mark

Liz Carney

Yes everything in the future is returning future. I'm not getting any next week, 2 weeks or 3 weeks.

O of course, great thought about completed projects!

Mark Cronk

Hi Liz,

OK, I think I have a solution. It's ugly but should work. The problem is the way Smartsheets counts weeks. On 1/2/21, WEEKNUMBER(Today()) is 53. So, we have to check for week 53 and adjust for future weeks that are greater than 52. It takes a bunch of nesting. Give it a try:

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row < TODAY(), "Overdue", IF(WEEKNUMBER(TODAY()) = 53, IF(WEEKNUMBER([Due Date]@row) = 53, "This Week", IF(WEEKNUMBER([Due Date]@row) = 1, "Next Week", IF(WEEKNUMBER([Due Date]@row) = 2, "2 weeks", IF(WEEKNUMBER([Due Date]@row) = 3, "3 Weeks", "Future")))), IF(OR(WEEKNUMBER(TODAY()) + 1 > 52, WEEKNUMBER(TODAY()) + 2 > 52, WEEKNUMBER(TODAY()) + 3 > 52), IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 51, "Next Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 50, "2 Weeks", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 49, "3 Weeks", "Future"))), IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 1, "Next Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 2, "2 Weeks", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 3, "3 Weeks", "Future")), "error"))))))

Mark

Liz Carney

Thank you @Mark Cronk! It works perfectly!

Mark Cronk

Happy to help. Thanks for using the Community.

Mark

TimJSchaaf

https://community.smartsheet.com/discussion/comment/273558#Comment_273558

Mark, I'm trying to replicate this and can't get it to work. I'm getting the #incorrect argument set error for anything past "Next Week". Ideally, I'd be able to have a formula that produces "Overdue", "This Week", "Next Week", "Upcoming".

Mark Cronk

Hi,

Try:

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row < TODAY(), "Overdue", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY())+1,"Next Week", "Upcoming"))))

Mark

TimJSchaaf

Thanks so much, Mark. It's amazing how long I can fuss over a formula and not be able to crack it. This was a gift for a Friday morning!

Mark Cronk

Anytime. Happy to help.

Mark