COUNTIFS date range less than 90 days from date in cell

Justin Ramos

Hey all, I can't find anything on this and struggling to come up with a formula looking at last 90 days from a set date. Please see attached screenshot for my current formula but I feel it shouldn't be this difficult and is probably something easy but currently stuck. My formula is not working because I have dates in the future which doesn't make sense. Thank you!

SS Screenshot.PNG

Khasim

Hi @Justin Ramos Could you please elaborate on your question?

Justin Ramos

This is my current formula,

=COUNTIFS({COMPLETED-Service Original install date}, AND(@cell >= weeks@row - 90, @cell <= weeks@row))

I am using a metrics roll up sheet to look at our service calendar. I need to know a count of services in the last 90 days from the original install date off the date@row (every Monday starting the week) and then I can know by week if we are seeing any trends.

As a company, we look at # of services created from an original install date in the last 90 days as the benchmark to know how efficient our installations are and I need to know what it was 90 days from the date at the cell.

Kelly Moore

Hey @Justin Ramos

To exclude the future dates add another term to your AND()

=COUNTIFS({COMPLETED-Service Original install date}, AND(@cell >= weeks@row - 90, @cell <= weeks@row, @cell<=TODAY()))

Will this work for you?

Kelly

Justin Ramos

I think I figured out why there are dates in the future, because the weeks@row-90. To get an accurate depiction of what I'm looking for, I just have to make a report showing only dates from today and the past since the moving 90 day window hasn't reached those future dates yet, if that makes sense. Thank you!

Kelly Moore

Did the adjustment that I added to the formula solve the future dates, or are you still having the same problem?