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

Matthew Schmookler

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

David Tutwiler

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")))

David Tutwiler

@Matthew Schmookler Going to ping you as well as I'm not sure how the notifications work on the OP end.

David Tutwiler

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))))

Matthew Schmookler

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.

Matthew Schmookler

https://community.smartsheet.com/discussion/comment/265204#Comment_265204

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.

David Tutwiler

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")))

David Tutwiler

@Matthew Schmookler Going to ping you as well as I'm not sure how the notifications work on the OP end.

Matthew Schmookler

@David Tutwiler 

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! 🙌

David Tutwiler

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