# Formula to return a status based on due date

Options

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!

Tags:

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

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.

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

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!

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

Thank you @Mark Cronk! It works perfectly!

• ✭✭✭✭✭✭
Options

Happy to help. Thanks for using the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

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!

• ✭✭✭✭✭✭
Options

Anytime. Happy to help.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!