Using a Formula in Report Builder for "When"

KatrinaRJ

I have multiple sheets containing employee's start dates on various projects. I want to build a report that draws from these sheets, but only displays the employees starting this Monday - with that date changing depending on when the report is viewed and what the upcoming Monday's date is.

Is there a formula or logic I can use in the "set criteria for" window, perhaps using "is equal to" and then inserting a formula to determine the next Monday?

If there's an easier way to accomplish this, please let me know. Thanks!

Mike Salazar

I would create a master roll up sheet and filter based on equals your criteria.

Andrée Starå

Hi @KatrinaRJ,

To add to Mike's excellent advice.

You'll need to add a so-called helper column to the sheets with a formula to show from the upcoming Monday. You would then select the criteria in the report builder.

Make sense?

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

KatrinaRJ

Thank you both! This question reveals my Excel ignorance, but what kind of formula would I use to find those dates in either a helper column or a rollup sheet? I googled and found formulas like WEEKDAY and WEEKNUM, but I don't know how to wrangle that into what I need.

To make it easier, I think as long as the formula displays dates in the coming week (instead of just "next Monday") that would work for my purposes.

Andrée Starå

@KatrinaRJ

Happy to help!

Does your week start on Mondays? If yes, we could use the WEEKNUMBER function. Something like,

IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) + 1)

Would that work?

KatrinaRJ

Thank you for the insights! I ended up taking this information and doing some research, and figured out a formula that would work for my purposes.

For reference, that formula is:

=IF(WEEKNUMBER([nameofcolumn]1)=WEEKNUMBER(TODAY())+1,1)

Andrée Starå

@KatrinaRJ

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!