Formula adjustment to handle the year-end cutover

System

This discussion was created from comments split from: Adding a year check to a "starts this week" formula.

Ryan_At_OSD

I am having similar trouble with the year-end start and due formulas. Ideally, I'd like to show the number of upcoming tasks based on the start, finish, and current date that handles the year-end cutover.

Level = 0 is the task level. Summary levels start at 1 and go up from there for each indent.

1. Start this week | =IFERROR(IF(Level@row = 0, IF(AND(YEAR(Start@row) = YEAR(TODAY()), WEEKNUMBER(Start@row) = WEEKNUMBER(TODAY())), 1)), "Data Needed")
2. Due this week | =IFERROR(IF(Level@row = 0, IF(AND(YEAR(Finish@row) = YEAR(TODAY()), WEEKNUMBER(Finish@row) = WEEKNUMBER(TODAY())), 1)), "Data Needed")
3. Start this month | =IFERROR(IF(Level@row = 0, IF(AND(YEAR(Start@row) = YEAR(TODAY()), MONTH(Start@row) = MONTH(TODAY())), 1, 0)), "Data Needed")
4. Due this month | =IFERROR(IF(Level@row = 0, IF(AND(YEAR(Finish@row) = YEAR(TODAY()), MONTH(Finish@row) = MONTH(TODAY())), 1, 0)), "Data Needed")
5. Start next month | =IFERROR(IF(Level@row = 0, IF(AND(YEAR(Start@row) = YEAR(TODAY()), MONTH(Start@row) = MONTH(TODAY()) + 1), 1, 0)), "Data Needed")
6. Due next month | =IFERROR(IF(Level@row = 0, IF(AND(YEAR(Finish@row) = YEAR(TODAY()), MONTH(Finish@row) = MONTH(TODAY()) + 1), 1, 0)), "Data Needed")
7. Start in two months |=IFERROR(IF(Level@row = 0, IF(AND(YEAR(Start@row) = YEAR(TODAY()), MONTH(Start@row) = MONTH(TODAY()) + 2), 1, 0)), "Data Needed")
8. Due in two months | =IFERROR(IF(Level@row = 0, IF(AND(YEAR(Finish@row) = YEAR(TODAY()), MONTH(Finish@row) = MONTH(TODAY()) + 2), 1, 0)), "Data Needed")

Since the year doesn't match at year-end, the formulas broke leading up to Jan 1, 2023. Any help you can provide will be greatly appreciated.

Toufong Vang

@Ryan_At_OSD

Hi, Ryan,

Give the following a try.

find_week (the expression below finds the week(s) between the Start@row and TODAY())

((YEAR(Start@row) - YEAR(TODAY()))*52 + WEEKNUMBER(Start@row) - WEEKNUMBER(TODAY()))

Start/Due this week when find_week = 0; i.e., IF( find_week = 0, 1) .

find_month (the expression below finds the month(s) between the Start@row and TODAY())

((YEAR(Start@row) - YEAR(TODAY()))*12 + MONTH(Start@row) - MONTH(TODAY()))

Start/Due this month when find_month = 0; i.e., IF( find_month = 0, 1, 0) .

Start/Due next month when find_month = 1; i.e., IF( find_month = 1, 1, 0) .

Start/Due in 2 months when find_month = 2; i.e., IF( find_month = 2, 1, 0); when "find_month" is replaced with the expression...IF(((YEAR(Start@row) - YEAR(TODAY()))*12 + MONTH(Start@row) - MONTH(TODAY()))= 2, 1, 0)

=IF(Level@row = 0, IFERROR(IF(((YEAR(Start@row) - YEAR(TODAY()))*12 + MONTH(Start@row) - MONTH(TODAY())) = 2, 1, 0),"Data Needed"))

Ryan_At_OSD

Thank you, I'll give it a try!