Week Labeling

DiAlmeida

Hello everyone! 

Hoping I can get some assistance from the Gurus around here.

Currently, we have an =IF formula that looks at the Due Date of a project and:

• if the project is today, it returns THIS WEEK
• if the project was last week, it returns LAST WEEK
• if it is next week, it returns NEXT WEEK
• if it is older than last week, it returns OLDER ITEM
• if it is more than one week in advance, it was supposed to return FUTURE, which is not working.
• otherwise, if there is no Due Date or theres an issue, it says PLEASE ADD DUE DATE

The formula currently in use is the bellow:

=IFERROR(IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([Due Date]@row) - WEEKNUMBER(TODAY()) = 1, "Next Week", IF(WEEKNUMBER([Due Date]@row) - WEEKNUMBER(TODAY()) = -1, "Last Week", IF(WEEKNUMBER([Due Date]@row) - WEEKNUMBER(TODAY()) < -1, "Older Item", "Future")))), "Please Add Due Date")

So what is happening today is that the "Future" bit is not working, and it just returns "Older Item" even if the project/activity is in the future.

 

Tried playing with it for a while and could not get it to work. Nor could I find anything else online.

 

Would appreciate any help.

 

Cheers.

mo'connor113211

Just wondering what happens if you reverse the test in the final IF statement:

From:

IF(WEEKNUMBER([Due Date]@row) - WEEKNUMBER(TODAY()) < -1, "Older Item", "Future")

To:

IF(WEEKNUMBER(TODAY()) - WEEKNUMBER([Due Date]@row) > 1, "Future", "Older Item")

Paul Newcome

A few minor adjustments. Give this a try...

.

=IFERROR(IF(WEEKNUMBER([Due Date]@row) >= WEEKNUMBER(TODAY()) + 2, "Future", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 1, "Next Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 1, "Last Week", "Older Item")))), "Please Add Due Date")

DiAlmeida

Hi all,

thank you for your responses, sorry it's been a while but we closed for the Christmas break. Happy 2020 btw.

So I have tried both formulas, and yes they work generally, however, if the week in the future is past a new year the week count resets, thus the formula returns as OLDER ITEM. Is there a way to flag that the week count might be in the new year?