Formula adaptions: to-dos "next week" when turn of the year happens
Hello to all,
I have created a board where tasks are sorted to be due like "overdue", "today", this week", "next week", "in two weeks".
The values are created through a formula, which in generally works really fine. But for "this week", "next week" and "in two weeks" I am facing a problem when it comes to the turn of the year.
Basically I compare weeknumbers. E.g. I compare the weeknumber of an end date to the weeknumber of today. If the difference is 0, then the task is due "this week". If the difference is 1, then it is due "next week". If the difference is 2, then it is due "in two weeks".
At the turn of the year, e.g. when the current weeknumber is 52, and the following weeknumber is 1, this comparsion doesn't work.
Also for due "this week" it can happen, that the weeknumbers are the same, but in different years :)
So the question is: how do I incorporate the year into the formula correctly? Or is there a smarter way to do it?
The formula looks like this at the moment:
=
IF(AND((Start@row)<>""; (Ende@row)<>"");
IF(AND((Ende@row) < TODAY(); "Overdue";
IF(AND((Ende@row) = TODAY(); "Today";
IF(AND(WEEKNUMBER(Ende@row) = WEEKNUMBER(TODAY()); "This week";
IF(AND(WEEKNUMBER(Ende@row) = WEEKNUMBER(TODAY())+1; "Next week";
IF(AND(WEEKNUMBER(Ende@row) >= WEEKNUMBER(TODAY())+2; "In two weeks";
""
))))))
Any good ideas? Thanks a lot for help :)
Cheers & big thanks,
Markus
Answers
-
Instead of doing
WEEKNUMBER(TODAY()) + 1
I would use
WEEKNUMBER(TODAY(7))
Then you can use an AND statement to join that using the same logic with the YEAR.
AND(WEEKNUMBER(Ende@row) = WEEKNUMBER(TODAY(7)), YEAR(Ende@row) = YEAR(TODAY(7)))
-
Hi Paul,
thanks a lot for that hint. I will give it a try! 👍
Cheers,
Markus
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!