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

bbemis

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!

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

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

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

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

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

Thanks so much for your help!