Formula adjustment to handle the year-end cutover

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

Answers

  • Ryan_At_OSD
    Options

    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
    Toufong Vang ✭✭✭✭✭
    Options

    @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
    Options

    Thank you, I'll give it a try!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!