Trending date formulas across new year

I would like to create a summary formula that automatically updates every week for work completed the previous week.

I have a date column that indicates completion date

I have a helper column that assigns a week number using WEEKNUMBER(date) called "Week Completed"

I have a summary formula as follows:

=COUNTIF([Week Completed]:[Week Completed],(@cell=WEEKNUMBER(TODAY())-1))

Everything worked until this week. Since 52 (the last week of the year) does not equal 1-1 (This week minus 1), I get no results.

Is there another way to right a formula for "last week" or "2 weeks ago" that will survive the new year reset?

Thanks for the help.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!