Show tasks completed in calendar week?

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

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

@Andrea Zenner Happy to help. 👍️

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.