Date Driven Report

Options
BESP10
BESP10 ✭✭✭✭✭✭
edited 12/01/20 in Smartsheet Basics

Good afternoon,

We need to be able to create a report for our sheets that generates from the current date (todays date) and any date within 4 weeks after that.

We have a begin date and end date column. How can we create a report that only shows what will be happening in the next 4 weeks?


Thank you

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/02/20 Answer ✓
    Options

    Hi @BESP10

    Hope you are fine, i created a sample sheet for your case and hope the following is answer your question:

    1- add new Date type column called ( Check date ) calculate it's value using the following formula =TODAY() + 28 to define the date starting from today with 4 week in future.

    2- add checkbox column (4 week Task filter) calculate it's value using the following formula =IF(OR(AND([begin date]@row > TODAY(), [begin date]@row < [Check date]@row), [end date]@row < [Check date]@row), true, false)

    3- prepare report using filter to show task with checked value for the column ( 4 week Task filter ).

    the following screen shot explain the solution :


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 12/02/20 Answer ✓
    Options

    Hi @BESP10

    Hope you are fine, i created a sample sheet for your case and hope the following is answer your question:

    1- add new Date type column called ( Check date ) calculate it's value using the following formula =TODAY() + 28 to define the date starting from today with 4 week in future.

    2- add checkbox column (4 week Task filter) calculate it's value using the following formula =IF(OR(AND([begin date]@row > TODAY(), [begin date]@row < [Check date]@row), [end date]@row < [Check date]@row), true, false)

    3- prepare report using filter to show task with checked value for the column ( 4 week Task filter ).

    the following screen shot explain the solution :


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Options

    Thank you @Bassam.M Khalil , I am almost there. I ran the report but dates are still showing up that are past the 28 days. We only want to see into the future for the next 4 weeks. What could I be doing wrong here?



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @BESP10

    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!

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @BESP10

    Our Reports recently had an update which have adjusted the way that the builder looks/works. Were you able to figure out how to set the dates?

    In the new Report builder, you would select the Date Column to use as a filter, and set it to be In the Next (number) Days:

    You can learn more in our Help Center, here.

    Cheers!

    Genevieve