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

leiti373 ✭✭
edited 04/13/23 in Formulas and Functions

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())+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,



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!