Formula to change status based on current week/due date
Hello. I am attempting to have a status change to 'This Week' if a due date column falls within the current WORK WEEK. I am having a few challenges - 1) the formula is changing what is due 'this week' depending on the current date, NOT the standard Mon-Sun week, 2) I want anything with a blank 'Due' date column to default to 'To Do' (this works occasionally depending on what order I put my formula), and 3) trouble with the basic formula, in general.
Columns I am using:
Status (To Do, This Week, Overdue), Start (Date), Due (Date)
What is happening now for today (Tuesday), my This Week list is showing what is due this week through next Tuesday, when I'd like it to stop on Sunday. I am also attempting to add an ISBLANK formula for those items that do not yet have a due date. I have been trying variations of the below formula without luck:
=IF(OR(Due@row > TODAY(), ISBLANK(Due@row)), "To Do", IF(Due@row < TODAY(), "Overdue", IF(Due@row <= TODAY(+7), "This Week", )))
I am thinking I may need to incorporate WEEKNUMBER for the issue with the week length? I have a column pulling the Week Number, but unsure how I would incorporate that.
Screen shot attached, which is utilizing this formula:
=IF(Due@row <= TODAY(+7), "This Week", IF(OR(Due@row > TODAY(), ISBLANK(Due@row)), "To Do", IF(Due@row < TODAY(), "Overdue")))
Help appreciated!
Best Answers
-
Genevieve P. Employee AdminHi@Natalia
You're absolutely correct, you can use theWEEKDAY functionto figure out what week day today is, then adjust your formula to have a different rule depending on the day. You are also correct in being careful about the order of your logic statements, because if theIF statementfinds a match, it will stop reading the rest of the statement.
There may be a cleaner way to do this, but this will work and it's how I would build this statement. I'll break it down per-rule, then show you all-together.
Blank Date
The first thing I'll start with the is Blank Due date, and ask that if it's blank, the status is "To Do"
=IF(Due@row = "", "To Do",
Past Date
Next, any date in the past should be "Overdue". I put this after the blank rule because blank cells are seen as "In the past".
如果今天(Due@row <(),“过期”,
This Week's date, but in the future or Today
Now it gets tricky. We need to say, if Today is this day in the week AND the Due date is within this range, return "This Week". To do this, I have one big OR statement that looks across all the day numbers, then within each weekday there is an AND statement to look at those two criteria:
IF(OR(
AND(WEEKDAY(TODAY()) = 1, Due@row <= TODAY(+7)),
AND(WEEKDAY(TODAY()) = 2, Due@row <= TODAY(+6)),
AND(WEEKDAY(TODAY()) = 3, Due@row <= TODAY(+5)),
AND(WEEKDAY(TODAY()) = 4, Due@row <= TODAY(+4)),
AND(WEEKDAY(TODAY()) = 5, Due@row <= TODAY(+3)),
AND(WEEKDAY(TODAY()) = 6, Due@row <= TODAY(+2)),
AND(WEEKDAY(TODAY()) = 7, Due@row <= TODAY(+1))),
If any of the above AND combinations are the case, then it will return:
"This Week",
Other dates in the future.
Finally, we can say that if anything is in the future (and doesn't match the above rules), mark it as "To Do".
IF(Due@row > TODAY(), "To Do"))))
Here's the full formula:
=IF(Due@row = "", "To Do", IF(Due@row < TODAY(), "Overdue", IF(OR(AND(WEEKDAY(TODAY()) = 1, Due@row <= TODAY(+7)), AND(WEEKDAY(TODAY()) = 2, Due@row <= TODAY(+6)), AND(WEEKDAY(TODAY()) = 3, Due@row <= TODAY(+5)), AND(WEEKDAY(TODAY()) = 4, Due@row <= TODAY(+4)), AND(WEEKDAY(TODAY()) = 5, Due@row <= TODAY(+3)), AND(WEEKDAY(TODAY()) = 6, Due@row <= TODAY(+2)), AND(WEEKDAY(TODAY()) = 7, Due@row <= TODAY(+1))), "This Week", IF(Due@row > TODAY(), "To Do"))))
Let me know if this works for you!
Cheers,
Genevieve
-
Genevieve P. Employee Admin
Ah, thank you!
是的,你可以使用ISBLANK而不是……但我preferred way is just to say <> "" ... or "not blank"
This would be the exact same as what we did earlier for checking the Due@row to see if it's not blank.
Try this:
=IF(Complete@row <> "", "Complete", IF(Due@row = "", "To Do", IF(Due@row < TODAY(), "Overdue", IF(OR(AND(WEEKDAY(TODAY()) = 1, Due@row <= TODAY(+7)), AND(WEEKDAY(TODAY()) = 2, Due@row <= TODAY(+6)), AND(WEEKDAY(TODAY()) = 3, Due@row <= TODAY(+5)), AND(WEEKDAY(TODAY()) = 4, Due@row <= TODAY(+4)), AND(WEEKDAY(TODAY()) = 5, Due@row <= TODAY(+3)), AND(WEEKDAY(TODAY()) = 6, Due@row <= TODAY(+2)), AND(WEEKDAY(TODAY()) = 7, Due@row <= TODAY(+1))), "This Week", IF(Due@row > TODAY(), "To Do")))))
Answers
-
Genevieve P. Employee Admin
Hi@Natalia
You're absolutely correct, you can use theWEEKDAY functionto figure out what week day today is, then adjust your formula to have a different rule depending on the day. You are also correct in being careful about the order of your logic statements, because if theIF statementfinds a match, it will stop reading the rest of the statement.
There may be a cleaner way to do this, but this will work and it's how I would build this statement. I'll break it down per-rule, then show you all-together.
Blank Date
The first thing I'll start with the is Blank Due date, and ask that if it's blank, the status is "To Do"
=IF(Due@row = "", "To Do",
Past Date
Next, any date in the past should be "Overdue". I put this after the blank rule because blank cells are seen as "In the past".
如果今天(Due@row <(),“过期”,
This Week's date, but in the future or Today
Now it gets tricky. We need to say, if Today is this day in the week AND the Due date is within this range, return "This Week". To do this, I have one big OR statement that looks across all the day numbers, then within each weekday there is an AND statement to look at those two criteria:
IF(OR(
AND(WEEKDAY(TODAY()) = 1, Due@row <= TODAY(+7)),
AND(WEEKDAY(TODAY()) = 2, Due@row <= TODAY(+6)),
AND(WEEKDAY(TODAY()) = 3, Due@row <= TODAY(+5)),
AND(WEEKDAY(TODAY()) = 4, Due@row <= TODAY(+4)),
AND(WEEKDAY(TODAY()) = 5, Due@row <= TODAY(+3)),
AND(WEEKDAY(TODAY()) = 6, Due@row <= TODAY(+2)),
AND(WEEKDAY(TODAY()) = 7, Due@row <= TODAY(+1))),
If any of the above AND combinations are the case, then it will return:
"This Week",
Other dates in the future.
Finally, we can say that if anything is in the future (and doesn't match the above rules), mark it as "To Do".
IF(Due@row > TODAY(), "To Do"))))
Here's the full formula:
=IF(Due@row = "", "To Do", IF(Due@row < TODAY(), "Overdue", IF(OR(AND(WEEKDAY(TODAY()) = 1, Due@row <= TODAY(+7)), AND(WEEKDAY(TODAY()) = 2, Due@row <= TODAY(+6)), AND(WEEKDAY(TODAY()) = 3, Due@row <= TODAY(+5)), AND(WEEKDAY(TODAY()) = 4, Due@row <= TODAY(+4)), AND(WEEKDAY(TODAY()) = 5, Due@row <= TODAY(+3)), AND(WEEKDAY(TODAY()) = 6, Due@row <= TODAY(+2)), AND(WEEKDAY(TODAY()) = 7, Due@row <= TODAY(+1))), "This Week", IF(Due@row > TODAY(), "To Do"))))
Let me know if this works for you!
Cheers,
Genevieve
-
Natalia Kataoka ✭✭✭✭✭
Ah ha! This is great and it works, thank you Genevieve!
-
Natalia Kataoka ✭✭✭✭✭
@Genevieve PWhoops, I forgot one more element!
I also need the formula to change the status to "Complete" if there is an entry in the "Complete" column. Is that an easy add?
-
Genevieve P. Employee Admin
Haha, so close!
Yes, that's a very easy add. Let's put that in at the very start, so that it's the first thing the formula checks:
=IF(Complete@row = "Complete", "Complete"
^^This presumes the value is "Complete" in that row. If it's a checkbox column, you can use 1 to indicate if it's checked:
=IF(Complete@row = 1, "Complete"
Full Formula:
=IF(Complete@row = "Complete", "Complete", IF(Due@row = "", "To Do", IF(Due@row < TODAY(), "Overdue", IF(OR(AND(WEEKDAY(TODAY()) = 1, Due@row <= TODAY(+7)), AND(WEEKDAY(TODAY()) = 2, Due@row <= TODAY(+6)), AND(WEEKDAY(TODAY()) = 3, Due@row <= TODAY(+5)), AND(WEEKDAY(TODAY()) = 4, Due@row <= TODAY(+4)), AND(WEEKDAY(TODAY()) = 5, Due@row <= TODAY(+3)), AND(WEEKDAY(TODAY()) = 6, Due@row <= TODAY(+2)), AND(WEEKDAY(TODAY()) = 7, Due@row <= TODAY(+1))), "This Week", IF(Due@row > TODAY(), "To Do")))))
Let me know if that makes sense!
Cheers,
Genevieve
-
Natalia Kataoka ✭✭✭✭✭
@Genevieve PMy Complete column is a date field, so I would want it to show Status = Complete if there is any date entered. Would that be a spot to use ISBLANK?
-
Genevieve P. Employee Admin
Ah, thank you!
是的,你可以使用ISBLANK而不是……但我preferred way is just to say <> "" ... or "not blank"
This would be the exact same as what we did earlier for checking the Due@row to see if it's not blank.
Try this:
=IF(Complete@row <> "", "Complete", IF(Due@row = "", "To Do", IF(Due@row < TODAY(), "Overdue", IF(OR(AND(WEEKDAY(TODAY()) = 1, Due@row <= TODAY(+7)), AND(WEEKDAY(TODAY()) = 2, Due@row <= TODAY(+6)), AND(WEEKDAY(TODAY()) = 3, Due@row <= TODAY(+5)), AND(WEEKDAY(TODAY()) = 4, Due@row <= TODAY(+4)), AND(WEEKDAY(TODAY()) = 5, Due@row <= TODAY(+3)), AND(WEEKDAY(TODAY()) = 6, Due@row <= TODAY(+2)), AND(WEEKDAY(TODAY()) = 7, Due@row <= TODAY(+1))), "This Week", IF(Due@row > TODAY(), "To Do")))))
-
Natalia Kataoka ✭✭✭✭✭
That did it! Thank you!!!
-
Genevieve P. Employee Admin
Wonderful! Let me know if you have any other questions.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
