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

Answers

  • Natalia Kataoka
    Natalia Kataoka ✭✭✭✭✭

    Ah ha! This is great and it works, thank you Genevieve!

  • Natalia Kataoka
    Natalia Kataoka ✭✭✭✭✭

    @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?

  • Genevieve P.
    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
    Natalia Kataoka ✭✭✭✭✭

    @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?

  • Natalia Kataoka
    Natalia Kataoka ✭✭✭✭✭

    That did it! Thank you!!!

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! Let me know if you have any other questions. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!