Dashboard that shows tasks due this week and next week

Hi,

I am trying to create a dashboard, based off a report, that shows tasks due for a given individual for the current week and also pulls tasks due for the next week (separate report for current week tasks and separate report for next weeks tasks). The only way I have found to make it so it shows tasks for the current week is to add a weekcheck column (Checkbox) to the project plan and add the formula to it: =IF(WEEKNUMBER(Start@row) = WEEKNUMBER(TODAY()); 1). However, I don't know how to tailor this formula to show the week after the current week so it only pulls the tasks for the following week. Is there a way to do that?

We initially had it setup to pull tasks within the next 7 days and then the next 14 days but since the dashboard is live, depending on the date they look at it, the next 7 days could be part of the current week and part of the next week. We some how want to make this static, so that is what I am trying to accomplish with the above. If there is a better way to do this too, let me know!

Thanks in advance!

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi!

    Try this in your weekcheck column: (you'll need to replace "DueColumn" with your column name. If it has a space in it.)

    =IF(WEEKNUMBER([DueColumn]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([DueColumn]@row) = (WEEKNUMBER(TODAY()) + 1), "Next Week", ""))

    This translates to: if the date in the due date field in this row is in the same week as today, show "This week." If the date in the due date field in this row is one week later than today's week, show "Next week." Otherwise, show blank.

    Hope this helps!


    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi!

    Try this in your weekcheck column: (you'll need to replace "DueColumn" with your column name. If it has a space in it.)

    =IF(WEEKNUMBER([DueColumn]@row) = WEEKNUMBER(TODAY()), "This Week", IF(WEEKNUMBER([DueColumn]@row) = (WEEKNUMBER(TODAY()) + 1), "Next Week", ""))

    This translates to: if the date in the due date field in this row is in the same week as today, show "This week." If the date in the due date field in this row is one week later than today's week, show "Next week." Otherwise, show blank.

    Hope this helps!


    Best,

    Heather

  • I think this will work. We will try this out, thank you!!

  • G1NCHY
    G1NCHY ✭✭

    Three formulas I used with Gantt.

    I use Occurring rather than Due to understand what tasks are occurring during a time frame, this will capture tasks occurring during a period not necessarily due during that period. Currently not available to filter easily.

    The Tasks below have a filter for each and are also setup with automation so I get a report before each day on the day's tasks via email. On Friday I get an automated email with the tasks for next week.

    Tasks Occurring Today

    =IF(AND(Start@row <= TODAY(), Finish@row >= TODAY()), 1, 0)

    Tasks Occurring This Week

    =IF(AND(WEEKNUMBER(Start@row) <= WEEKNUMBER(TODAY()), WEEKNUMBER(Finish@row) >= WEEKNUMBER(TODAY())), 1, 0)

    Tasks Occurring Next Week

    =IF(AND(WEEKNUMBER(Start@row) <= WEEKNUMBER(TODAY()) + 1, WEEKNUMBER(Finish@row) >= WEEKNUMBER(TODAY()) + 1), 1, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!