Rolling 12 Months using AVG COLLECT formula help

Tina Davis

Hi, I am looking for some help. I am using the above SUMIFS, however doing an AVG COLLECT with two criteria. I can get the above formula to work for 6 months, however I am at a loss to get it to work for 12 months in the past. Below is my current formula and would appreciate any help.

=ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

Paul Newcome

I provided a link in my last comment. Is it not working?

Basically I suggested that you use 365 days (or -183 to +183) instead of trying to use dates.

AND(@cell>= TODAY(-183), @cell<= TODAY(183))

Paul Newcome

Did you see my comment on the other thread from earlier today?

https://community.smartsheet.com/discussion/comment/386754#Comment_386754

Tina Davis

Hi Paul, I don't see it. Can you direct me to it?

Paul Newcome

I provided a link in my last comment. Is it not working?

Basically I suggested that you use 365 days (or -183 to +183) instead of trying to use dates.

AND(@cell>= TODAY(-183), @cell<= TODAY(183))

Tina Davis

Thank you, I will give it a try!

Tina Davis

It worked perfectly, thank you so much and I am so sorry I didn't see the early post:)

Paul Newcome

Happy to help. 👍️