Dashboard that shows tasks due this week and next week

Casey Swiderski

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!

Heather Duff

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

Heather Duff

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

Casey Swiderski

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

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)

Shaam007

Hi there, I would like to confirm if these formulas are for excel? If they are then can they be used directly in excel cells? or are these VBA?

Andrée Starå

https://community.smartsheet.com/discussion/comment/464964#Comment_464964

Hi,

I hope you're well and safe!

They are for Smartsheet, but in some cases, they are very similar to Excel and can be easily adapted.

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Shaam007

https://community.smartsheet.com/discussion/comment/464965#Comment_464965

Thank you for such a quick response. Can i get help regarding Excel? Because i am trying to do the same thing but in excel dashboard for my project and i would like the upcoming tasks shown in overview sheet. Can you help?