Need a formula that works like a report -- date is within the last 30 days

bbemis
bbemis ✭✭✭
edited 12/09/19 in Formulas and Functions

I've been looking through the Community and can't find this...sorry if I've overlooked. 

I have a series of reports that determine if certain projects are within the last 30 days, such as added in the last 30 days, go live in the last 30 days, completed in the last 30 days, etc.  This is very easy to do on a report and get a list of all the projects that fit this criteria.  However, I'm trying to create a dashboard with metrics, and I can't seem to recreate this summary in a formula.

Let's start with an easy one. I have a column that is Go Live.  I want to do a COUNTIF for all projects where the Go Live date is within the last 30 days.  Is this possible to do? Or do I need to add an additional column on my spreadsheet to check there and just add the checkboxes?

Thanks in advance!

Tags:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    This is very doable. I would use Countifs, in case you wanted to add another criterion. 

    =COUNTIFS([Go Live Date Column Name]:[Go Live Date Column Name], <TODAY(-30), [Status column]:[Status column], <> "Complete")

    The formula above would check for the number of go-live dates less than today -30 but also that are not listed as complete (if you have a status column.) You can replace "Complete" with whatever you want. 

    Hope that helps! 

     

  • bbemis
    bbemis ✭✭✭
    edited 03/19/18

    I did try this.  I thought it should be a simple metric.  But my report and my metric are returning different values.

    BUT - I think I just realized that I've been excluding items on my report that I hadn't brought into my metric (with the COUNTIFS)...thank you for making me compare the two  :)  

    However, this number gives me ALL projects that have gone live...it isn't limited to the past 30 days...what am I missing?  The date part of the formula doesn't match what gets pulled in the report with "in the last 30 days".

  • bbemis
    bbemis ✭✭✭

    All right!!  I figured out the issue...it needs to be this:

    =COUNTIFS({Projects - Go LIve}, <TODAY(1), {Projects - Go LIve}, >TODAY(-30))

    This captures the projects that went live in the past 30 days.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Also, check to make sure my dates are correct. It may be that you need today(30) Which would be 30 days from today. IF you are looking for projects that are set to go live in the next 30 days. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 03/19/18

    Duh! (I should have caught that!) Yes, you need the qualifier to limit to the 1st day too. haha. Good job. Glad you could get it figured out. 

  • bbemis
    bbemis ✭✭✭

    Thanks so much for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!