Formula adaptions: to-dos "next week" when turn of the year happens

leiti373

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

Paul Newcome

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

leiti373

Hi Paul,

thanks a lot for that hint. I will give it a try! 👍

Cheers,

Markus

Paul Newcome

Happy to help. 👍️