Formula Help: Do This Week, Do Next Week

Laurin

Hi, gang! I need to create a formula that populates Do This Week, Do Next Week, Do Three Weeks for my boss.

I have Start and Finish date columns as well as a dropdown Do column with those three as options. We're not looking for reports, but rather this summary view in Card View for him. Thank you for your help!

jmyzk_cloudsmart_jp

Hi @Laurin

If what you are trying to do is calculate a list of Do's based on the Start date, the formula would be

=IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 0, "Do This Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 1, "Do Next Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 1, "Do Three Week", "")))

https://app.smartsheet.com/b/publish?EQBCT=66b3df5203e640e1bc8dd3a17f9f0e35

jmyzk_cloudsmart_jp

Hi @Laurin

If what you are trying to do is calculate a list of Do's based on the Start date, the formula would be

=IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 0, "Do This Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 1, "Do Next Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 1, "Do Three Week", "")))

https://app.smartsheet.com/b/publish?EQBCT=66b3df5203e640e1bc8dd3a17f9f0e35

Laurin

You're amazing; thank you jmyzk_cloudsmart_jp!

jmyzk_cloudsmart_jp

Happy to help. 😁

jmyzk_cloudsmart_jp

Laurin

@jmyzk_cloudsmart_jp, et al -

I'm really struggling to successfully add to this formula. I'm trying to add a 4th element, "Later" that would apply to any task starting in more than 3 weeks. This was my last attempt:

=IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 0, "Do This Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 1, "Do Next Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 1, "Do Three Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 2, “Later”, ””))))

Apart from the new request my boss is SUPER stoked. Thanks so much!

jmyzk_cloudsmart_jp

Hi @Laurin

" IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 3" needs to come erlier then " "IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 1" because ">1" includes ">3".

Do Later Column Formula:

=IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 0, "Do This Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) = 1, "Do Next Week", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 3, "Do Later", IF(WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()) > 1, "Do Three Week", ""))))

Here is the updated published sheet.

https://app.smartsheet.com/b/publish?EQBCT=66b3df5203e640e1bc8dd3a17f9f0e35

Laurin

@jmyzk_cloudsmart_jp you're a genius, SS hero in my book. Thanks so much! The explanation is HUGE - the lightbulb has finally turned on here. The elevator made it to the top floor. 😆 Have a great day!

jmyzk_cloudsmart_jp

Happy to help!! 😁