# Formula for column "Due Date" to change Column "To Do" (today, this week, this month, this year)

Hi All,

As mentioned in the title, trying to create a formula on the task tracker template that would toggle the "To Do" column to either "Today", "This Week" , "This Month" , or "This Year" based on the date of the "Due Date" column. Is this possible? If so, what would be the formula?

Any help with this is much appreciated.

Warmly,

Matthew

• Thanks for the @mention as I wasn't pinged on the first reply.

I think you have 3 issues/improvements with that formula, 1 being a technicality and one being a logic issue.

1) To do "not equal to" it's <>. I replaced that and the Unparseable went away, but I don't think it is calculating right because the things that should say "This Month" or "This Year" still said "This Week"

2) I think you used the > sign when you needed to use the = sign in the formula. For instance, if it needs to be done this week then you want make sure the WEEKNUMBER for today's due date is = (not >) the Due Date. I replaced that on both the week and the month

3) The year is essentially "if not any of the rest of this, then it is This Year". So rather than solving more logic I just put it in the False position of the last statement.

Hopefully this works for you.

=IF([Due Date]@row = TODAY(), "Today", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(MONTH([Due Date]@row) = MONTH(TODAY()), "This Month", "This Year")))

• Not 100% clear on what you're doing here, but I think a series of nested IFs would get you there. Something like:

=IF([Due Date]@row = TODAY(), "Today", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Month", etc, etc))))

• Hi David,

Thank you for your reply. I'll be honest, I'm very new to spreadsheet formulas, so please forgive my lack of clarity on this. Based on what you've presented here I came up with this formula to spit out what I'm looking for, but is comes up with "UNPARSEABLE"

=IF([Due Date]1 = TODAY(), "Today", IF(WEEKNUMBER([Due Date]1) > WEEKNUMBER(TODAY()), "This Week", IF(MONTH([Due Date]1) > MONTH(TODAY()), "This Month", IF(MONTH([Due Date]1) ≠ MONTH(TODAY()), "This Year"))))

I'm probably doing multiple things wrong, if not everything.

• I'm not sure if my last comment pinged you so I'm quoting you hear. My follow-up question is above. thanks for your help.

• Thanks for the @mention as I wasn't pinged on the first reply.

I think you have 3 issues/improvements with that formula, 1 being a technicality and one being a logic issue.

1) To do "not equal to" it's <>. I replaced that and the Unparseable went away, but I don't think it is calculating right because the things that should say "This Month" or "This Year" still said "This Week"

2) I think you used the > sign when you needed to use the = sign in the formula. For instance, if it needs to be done this week then you want make sure the WEEKNUMBER for today's due date is = (not >) the Due Date. I replaced that on both the week and the month

3) The year is essentially "if not any of the rest of this, then it is This Year". So rather than solving more logic I just put it in the False position of the last statement.

Hopefully this works for you.

=IF([Due Date]@row = TODAY(), "Today", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(MONTH([Due Date]@row) = MONTH(TODAY()), "This Month", "This Year")))

• Brilliant! It worked! Much appreciated! And honestly....I think we are on to something with this formula. If you check out the personal task tracker template in SS, you'll see this is a huge upgrade to the template ;)

Good stuff man! 🙌

• @Matthew Schmookler Awesome! Glad we got it to what you were looking for.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!