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
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
Best Answer
Answers
-
I found this post that answered my question. Thank you @Paul Newcome
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
-
Glad you were able to find a working solution! 👍️
-
@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!!
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
-
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))))
-
@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!
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
-
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))))
-
@Paul Newcome Brilliant! THANK YOU SO MUCH!
Andrea Zenner
Program Manager | Infrastructure & Operations
Apogee Enterprises
EAP | Mobilizer | Core Product Certified | Superstar
azenner@apog.com
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 142 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!