Formula for COUNTIFS and Dynamic Date Range

SMacey

Hi Smartsheet Community,

I am really struggling to get the right formula and have tried so many now I am losing it! I am trying to prepare dashboards for weekly project meetings based on SLAs. I need to look at the count sitting in a particular status that have a Contract Date of >28 days, 14-28 days and <14 days. Because it is a dashboard, I need the date ranges to be dynamic, therefore am trying to utilize the TODAY function, but I cannot get the right calculations or avoid #UNPARSABLE errors.

=COUNTIFS({Status}, "1. Contracted", {CW Date}, >TODAY(-28))

Here is my source:

Here is my summary sheet I am working on for the dashboard - I tried adding helper columns with today's date and then calculated the 28-day and 14-day dates based off today's date and using a formula, but I cannot figure it out

=COUNTIFS(({CW Date}, >Dates8, {Status}, "1. Contracted")

Do you have any suggestions for another formula, or am I on the right track and just have something off in my formula?

Nic Larsen

Try:

=COUNTIFS({Status}, "1. Contracted", {CW Date}, @cell >= TODAY(-28))

Nic Larsen

Try:

=COUNTIFS({Status}, "1. Contracted", {CW Date}, @cell >= TODAY(-28))

SMacey

Thanks @Nic Larsen - That worked!

Nic Larsen

Glad it worked. If you need to do a range, it would be something like this at the end:

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