Calculations and Dashboard Data based on WeekNumber

Randy Van Winkle

I've been asked to create a PM tracking system. All the tasks for the year are in one sheet with each task having a row. I a summary section that feeds a dashboard with data showing data points, charts, and reports based on the month and completion status. The colleague came back with request to revise the dashboard with stats based on the calendar week rather than a rolling 7 day or monthly rollup. Not sure how to accomplish this. I created a formula column to calculate the WeekNumber of the date scheduled and Date Completed. However, not sure how to show dynamic status on the dashboard based on the results of the current or previous WeekNumber

Here is the rough setup of the sheet (example)

Colleague Notes in red from draft dashboard

Any help greatly appreciated

parulmishra

There are two ways to do it You can have a sheet which contains all the Week numbers and use the formula

or another way is by using Summary fields. You can have one Summary field called "Week Number" Drop down Type having numbers from 1 to 52

and Second summary field having the above formula

=COUNTIFS(Task}, NOT(ISBLANK(@cell)), {DaySchduled}, WEEKNUMBER(@cell) =[Week Number]#, {DaySchdueledYear},"2023")

As you change the Week Number the Count of task will keep on changing