Do we have report filters that can filter something like "Last-Week" Workdays only?

Edidiong Obot · 03/10/22

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.

Jeff Reisman · 03/10/22

@Edidiong Obot

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.

Jeff Reisman · 03/10/22

@Edidiong Obot

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.

Edidiong Obot · 03/10/22

@Jeff Reisman Thank you very much. This is the quickest resolution I have and it works like a champ!

Jeff Reisman · 03/10/22

https://community.smartsheet.com/discussion/comment/322195#Comment_322195

You're welcome, happy that it helped.

Edidiong Obot · 03/16/22

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.

Jeff Reisman · 03/16/22

@Edidiong Obot

That's fantastic. I'm sure this will help somebody down the road. Nice work!

Do we have report filters that can filter something like "Last-Week" Workdays only?

Best Answer

Answers

Categories