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

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!

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…