Changing dates in reports

John McAlonan

I have a dashboard that we use every Monday to plan work for the next few weeks. I have a number of reports that look at jobs and other tasks that are starting this week or next week. I'd like to put a date filter that uses the term 'This Week' or 'Next Week' instead of putting in the actual dates. I'd also like to be able to define my weeks to start on Monday and end on Sunday. Same goes for months. We use accounting months that are always different from calendar months. I'd like to have 'This month' and 'Next month' terms. The current 'Next X days' doesn't work for this need.

Paul Newcome

Try using helper columns with formulas in your sheet. You could use checkbox type columns that are titled something along the lines of "Weekly Report" and "Monthly Report".

In the Weekly Report checkbox column, you could use a formula such as

=IF(OR(WEEKNUMBER([Date Column]@row) = WEEKNUMBER(TODAY()), WEEKNUMBER([Date Column]@row) = WEEKNUMBER(TODAY()) + 1), 1)

This will check the box for any rows where the week number is the current week or the next week. You can use this same logic and the MONTH function to look for the current month and next month.

Then you can base your reports off of whether or not this box is checked.

John McAlonan

I see where you're going with this approach. It's a brute force solution that should work for weeks. My months aren't standard but since those don't change as often the administrative burden of updating reports isn't as onerous.

Paul Newcome

Depending on your month requirements though, we may be able to come up with a formula for you.

I generally do things this way because it makes the report build so much more straight forward than trying to incorporate a bunch of different criteria.