Reporting progress between date ranges

ilene_healy68056

Dear Community,

I have searched the community for some solutions for what I am trying to achieve…but can’t quite get there.  I am working on some metrics around planned vs actual progress that I will plot on a chart.  I have created a ‘metrics’ sheet to count the tasks planned to be complete at the lowest outline level from my detail sheet based on End Date (which is the planned end date).   I would like to report progress bi-weekly, so have added in the date beginning with Jan 1 and then adding 14 days to create a reporting schedule.  I have also added WEEKNUMBER. 

I have run into 2 problems:  Problem #1, the week number I was reporting against was pulling in tasks for both the year 2019 and 2020 (eg. Weeknumbers 1, 2, 3, etc., for 2019 and weeknumber 1, 2, 3, etc.,  for 2020 were being counted together in my 2019 report).  So, I added the Year to ensure that weeknumbers in 2019 only were being considered.  That lead to problem #2, where now the end of the year dates, such as 12/31/2019, is being counted as  Weeknumber 1 + YEAR 2019 based on my formula.  BUT, this should be Weeknumber 1 for 2020, so I don’t want it in my 2019 report. 

Here is my formula in column “Planned Cumulative count Stage Gate Completion”.  This should pull in the cumulative count of tasks due for the site Heist for my bi weekly reporting schedule.

COUNTIFS({EndDateWeekNumber_EMEA TC1}, <=WEEKNUMBER([Reporting DATE (MM/DD/YY)]@row), {Planned End Date_EMEA TC1}, YEAR(@cell) = YEAR([Reporting DATE (MM/DD/YY)]@row), {OutLineLevel_EMEA TC1}, 4, {Site_EMEA TC1}, $[Region Site WeekNum]$3)

Attached is my metrics sheet and my referenced source sheet including an example of the 2019 dates that should be 2020 week 1.

Any ideas for how to get Week 1 to correctly be 2019 calendar week only?  Maybe including week number was overengineering it?

Thanks for your help!

Ilene

Eric M Oliveira

Hello,

Happy to help, given the formula provided my suggestion would be to utilize the End Date itself and not the WEEKNUMBER. From the formula provided changing the first criteria from <=WEEKNUMBER([Reporting DATE (MM/DD/YY)]@row) to <=[Reporting DATE (MM/DD/YY)]@row, then altering the column it is referencing, would achieve the desired goal. 12/31/19 is counting as week 1 because it is in the same week as the 1st week of the new year, 12/30/19 would count as well. In the past customers have corrected this utilizing an IF function to recognize the specific week or date or year and then subtracted 1. But for your formula based on the description provided it seems this may be corrected simply by referencing the date. 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

ilene_healy68056

Hi Eric

Thank you soooo much!  That did the trick!

I appreciate your help

Ilene