Count form submissions by current and previous week

Hi SmartSheet Community,

I'm hoping one of you can help me with this problem- I'm collecting incident reports with a form and would like to report on a dashboard how many incidents came in for this current week, and last week.

I have two checkbox columns in my sheet, one for current week, and one for last week and using the following formulas, was able to check the box dynamically week over week. I then use the summary tab to add these up using countifs and a few different criteria/categories.

Current Week: =IF(AND(YEAR(TODAY()) = YEAR(RecordedDate@row), WEEKNUMBER(TODAY()) = WEEKNUMBER(RecordedDate@row + 1)), 1)

Last Week: =IF(WEEKDAY(RecordedDate@row) > 1, IF(AND(YEAR(TODAY()) = YEAR(RecordedDate@row), WEEKNUMBER(TODAY()) - 1 = WEEKNUMBER(RecordedDate@row + 1)), 1))

The issue that I'm experiencing is with using the week number formula which only captures dates from Mon-Sun but I need to count Sunday-Saturday. I've see a few helpful posts from other talented users but so far have not been able to solve this issue.

Example:

The current week should be 7/17 - 7/23.

Last Week should be 7/10 - 7/16 but only form submissions from 7/11 - 7/17 are flagged- thus the counts are skewed.

I greatly appreciate any help you can share.

Find more posts tagged with

Accepted answers

All comments

Rochelle from Agilize

Hi Michael! Your last week formula is using a WEEKDAY function, so it will exclude Sat-Sun.

Try this for last week:

=IF(AND(YEAR(TODAY()) = YEAR(RecordedDate@row), WEEKNUMBER(TODAY()) - 1 = WEEKNUMBER(RecordedDate@row)), 1)

However, I did notice for the week you mentioned that it will count the 17th for week 28 and 7/10 for week 27. Either way, you're weekends will be included in the counts.

Try this for current week:

=IF(AND(YEAR(TODAY()) = YEAR(RecordedDate@row), WEEKNUMBER(TODAY()) = WEEKNUMBER(RecordedDate@row)), 1)

Stay Agile,

agilizeconsulting.com

Michael D.

Thank you Rochelle, this helps a great deal!

Out of curiosity, for "last week" it excludes any submissions from 7/10 which is the Sunday for that week. Generally, most submissions come in M-F, however in this case we have 5 that came in on a Sunday (7/10) and are excluded from the weekly count for week 27.

It looks like by default the weeknumber formula will count Monday-Sunday, not Sunday-Saturday. Here is another resource I've been referencing regarding this issue but I haven't been able to resolve it. https://community.smartsheet.com/discussion/78475/current-week-and-next-week-formula

Any creative ideas to account for this in the formula so submissions between Sunday - Saturday will be grouped together?

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.