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
-
Hi @Natalia
You're absolutely correct, you can use the WEEKDAY function to 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 the IF statement finds 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".
IF(Due@row < TODAY(), "Overdue",
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
-
Ah, thank you!
Yes, you could use ISBLANK and NOT... but my 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
-
Hi @Natalia
You're absolutely correct, you can use the WEEKDAY function to 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 the IF statement finds 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".
IF(Due@row < TODAY(), "Overdue",
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
-
Ah ha! This is great and it works, thank you Genevieve!
-
@Genevieve P Whoops, 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?
-
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
-
@Genevieve P My 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?
-
Ah, thank you!
Yes, you could use ISBLANK and NOT... but my 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")))))
-
That did it! Thank you!!!
-
Wonderful! Let me know if you have any other questions. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!
