Report not Matching Formula Issue

grant53396
edited 12/09/19 in Formulas and Functions

Hi,

I have created a report to use on a dashboard for showing the number of job cards to print for our workshop. The report is shown below, and you can see that there are different amount of jobs to be printed for each workshop:

Report Image

You will see on here that there is:

  • 3x for the FASTRACK Workshop
  • 10x for the Van Workshop
  • 1x for the Custom Workshop

The criteria for the report is:

  • Profitability Check Date is NOT BLANK
  • Printed? is BLANK
  • Intended Start Date is IN THE NEXT 7 DAYS

However, I have a formula that is simply counting these to try and come up with a metric to show on the dashboard, but the formula i have simply does not match this? Please can you advise what is wrong with the formula?? Or is it something more basic than that ;-)! (Formula is shown in the cells next to the live function)

Metric Formula

Look forward to hearing from you!!

Report Image.png

Metric Formulas.png

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The issue is with your start date portion. Your formula is basically looking for any date that is 8 or more days away. Not a date that is within the next 7.

     

    Try replacing your start date criteria with this...

     

    =COUNTIFS(............................................., {Date Range}, AND(@cell >= TODAY(), @cell <= TODAY(7)))

    .

    This says anything where the date is greater than or equal to today and less than or equal to 7 days in the future.

    thinkspi.com

  • Excellent! That makes total sense, and has sovled the issue. MT.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    thinkspi.com