Formula for COUNTIFS and Dynamic Date Range

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?

Find more posts tagged with

Formulas

Accepted answers

Nic Larsen

Try:

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

All comments

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()

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.