Show tasks completed in calendar week?

Andrea Zenner

Is there a way to count the number of tasks completed in a calendar week, Sunday-Saturday?

I have a metric that shows the number of items completed in the last 7 days (>TODAY(-7)) and I've been asked if the metric can show items completed in the calendar last week, meaning Sunday to Saturday, rather than a rolling last 7 days.

Is that possible?

TIA!

Andrea

Paul Newcome

@Andrea Zenner Happy to help. 👍️

Andrea Zenner

I found this post that answered my question. Thank you @Paul Newcome

https://community.smartsheet.com/discussion/70367/need-formula-to-pull-data-from-last-week-sun-through-sat

Paul Newcome

Glad you were able to find a working solution! 👍️

Andrea Zenner

@Paul Newcome Now they want it to be current week, not last week. So this past Sunday to the coming Saturday. How would I adjust the formula to do that? Here is the formula:

=COUNTIFS({160 Front Street Prod : Frame Type}, "Typical", {160 Front Street Prod Sched : Assembly Finish}, AND(@cell <= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 7)), @cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 13, 6))))

Thanks!!

Paul Newcome

Let's try this:

=COUNTIFS({160 Front Street Prod : Frame Type}, "Typical", {160 Front Street Prod Sched : Assembly Finish}, AND(@cell <= TODAY() - MOD(WEEKDAY(TODAY()), 7), @cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 6, -1))))

Andrea Zenner

@Paul Newcome Something's not quite right. The formula is returning zero when it should return 2. We have 2 items where the completed date is on the 29th that are not being pulled in.

Thanks!

Paul Newcome

Sorry about that. Give this a whirl:

=COUNTIFS({160 Front Street Prod : Frame Type}, "Typical", {160 Front Street Prod Sched : Assembly Finish}, AND(@cell <= TODAY() + (7 - WEEKDAY(TODAY())), @cell >= TODAY() - (MOD(WEEKDAY(TODAY()), 7) + IF(WEEKDAY(TODAY()) = 7, 6, -1))))

Andrea Zenner

@Paul Newcome Brilliant! THANK YOU SO MUCH!

Paul Newcome

@Andrea Zenner Happy to help. 👍️