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!

• ✭✭✭✭✭

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?

• ✭✭✭✭✭

That did it! Thank you!!!