Do we have report filters that can filter something like "Last-Week" Workdays only?
I need a filter to filter performed task in a weekly basis without inputting the dates.
Currently I use the filter "between ddmmyy and ddmmyy to create the reports. This is time consuming because I have to reset the dates manually weekly and with a lot of projects, it is gradually becoming unsustainable.
Now, I can see "in the past xdays" but this considers all days and counts from the current day, so, it does not give me the desired result.
I need some kind of a report filter to have a view of work done in a weekly manner for "Last Week", "Current Week" without using dates in the filter. Any help will be appreciated.
Best Answer
-
Consider a helper column utilizing the WEEKNUMBER function.
=WEEKNUMBER(TODAY()) - WEEKNUMBER(YourDateColumnName@row)
If the date on that row is in the current week, this should equal 0. If it's last week, this will equal 1.
For filtering for rows in the current week, set your filter to give you rows where helper column = 0; for rows from last week, helper column = 1.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Consider a helper column utilizing the WEEKNUMBER function.
=WEEKNUMBER(TODAY()) - WEEKNUMBER(YourDateColumnName@row)
If the date on that row is in the current week, this should equal 0. If it's last week, this will equal 1.
For filtering for rows in the current week, set your filter to give you rows where helper column = 0; for rows from last week, helper column = 1.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Thank you very much. This is the quickest resolution I have and it works like a champ!
-
You're welcome, happy that it helped.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
So I ran into an issue when the programme lasted beyond a year. So, I had to amend the formula as shown below for the helper columns and it worked.
For weekly report,
=IF(YEAR([Planned Start]@row) > YEAR(TODAY()), WEEKNUMBER(TODAY()) - (WEEKNUMBER([Planned Start]@row) + (52 - WEEKNUMBER(TODAY()))), WEEKNUMBER(TODAY()) - (WEEKNUMBER([Planned Start]@row)))
For monthly report,
=IF(YEAR([Planned Start]@row) > YEAR(TODAY()), MONTH(TODAY()) - (MONTH([Planned Start]@row) + (12 - MONTH(TODAY()))), MONTH(TODAY()) - (MONTH([Planned Start]@row)))
Then I applied the conditions as stated above by @Jeff Reisman
For filtering for rows in the current week/Current Month, set your filter to give you rows where helper column = 0; for rows from last week/Last Month, helper column = 1
I hope this will help some other persons in the future.
-
That's fantastic. I'm sure this will help somebody down the road. Nice work!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives