Using a Formula in Report Builder for "When"

Using a Formula in Report Builder for "When"

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!

Answers

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

  • Andrée Starå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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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å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?

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • 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å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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.