Filter since day of week

Rory Wohl

We have two scrum meetings per week (Monday and Wednesday) I want to be able to filter my sheet based on tasks I've completed since the last meeting. Is there a(n easy) way to create a date filter along the lines of "since last Monday"

Thanks!

Paul Newcome

Ok. Then you are going to want to use 3 Sheet Summary Fields.

The first two (the ones in the screenshot) are both dropdowns. The first has each day of the week. The second has the option of "Current Week" and "Previous Week".

The second is a date type field (I called mine "Start of Reporting") and contains the following formula:

=TODAY() - (WEEKDAY(TODAY()) - IF(Day# = "Sunday", 1, IF(Day# = "Monday", 2, IF(Day# = "Tuesday", 3, IF(Day# = "Wednesday", 4, IF(Day# = "Thursday", 5, IF(Day# = "Friday", 6, IF(Day# = "Saturday", 7)))))))) - IF([Week Selection]# = "Previous Week", 7)

Then you are going to want to add a checkbox column to the sheet. In this column you are going to want to use:

=IF([Completed Date]@row>= [Start of Reporting]#, 1)

Then you can either activate a filter or pull a report based on the checkbox column.

Paul Newcome

The easiest way I can think of would be to use a checkbox with a formula to automatically check boxes based on the completed date. The exact formula would depend on some of the details.

You said "Last Monday", so does that mean if today is Wednesday you are only going back 2 days, or are you going back to Monday of last week?

Rory Wohl

"so does that mean if today is Wednesday you are only going back 2 days"

Exactly, the "Wednesday" filter would be a two day "look back" (Tuesday, Monday) and the "Monday" filter would be a four day look back (Sunday, Saturday, Friday, Thursday)

Paul Newcome

Ok. So you aren't necessarily going back to Monday. You are going back to the day after the previous meeting.

Are you only needing this filter on Mondays and Wednesdays, or do you want it to also be applicable any other day of the week?

Rory Wohl

Right now, our status meetings are on Mondays & Wednesdays, but that might not always be the case. It would be great if the filter could handle "going back" to any day of the week.

Paul Newcome

Do you have access to Sheet Summary fields?

Rory Wohl

Yes, I do.

Paul Newcome

Ok. Would you want to manually enter a date, or would you prefer to select a day of the week (mon, tues, wed, etc) and then select previous or current week from dropdowns?

So either you would have a single date field in the Sheet Summary where you can manually enter a date, or you would have two dropdowns in the Sheet Summary such as the below screenshot.

Rory Wohl

I really like the option of being able to select the day of the week and the week itself as you show in the screenshot.

Paul Newcome

Ok. Then you are going to want to use 3 Sheet Summary Fields.

The first two (the ones in the screenshot) are both dropdowns. The first has each day of the week. The second has the option of "Current Week" and "Previous Week".

The second is a date type field (I called mine "Start of Reporting") and contains the following formula:

=TODAY() - (WEEKDAY(TODAY()) - IF(Day# = "Sunday", 1, IF(Day# = "Monday", 2, IF(Day# = "Tuesday", 3, IF(Day# = "Wednesday", 4, IF(Day# = "Thursday", 5, IF(Day# = "Friday", 6, IF(Day# = "Saturday", 7)))))))) - IF([Week Selection]# = "Previous Week", 7)

Then you are going to want to add a checkbox column to the sheet. In this column you are going to want to use:

=IF([Completed Date]@row>= [Start of Reporting]#, 1)

Then you can either activate a filter or pull a report based on the checkbox column.

Rory Wohl

Super cool! Thanks!

Paul Newcome

Happy to help. 👍️

Feel free to revisit if you encounter any bugs or would like to tweak it.