Calculate Aging "in buckets" for Open Records (on a Metrics Sheet)

I am doing aging, but my numbers are off by one. I have exported the file, but I cannot find where the issue is. But that is when I noticed that my formulas were NOT correct (How I wanted them). The formulas are all based on a Status = Open. I realized for my middle formulas, I should have used AND and included the max range, but when I attempted, "Unparsable". My Worksheet comes out with 12 total for Open Records. However these formulas display 13 records, which means something is not being counted properly.

Less than 30 Days

=COUNTIFS({Initial Escalation Date}, >WORKDAY(TODAY(), -29), {Status}, ="Open")

30 - 59 Days

=COUNTIFS({Initial Escalation Date}, <=WORKDAY(TODAY(), -30), {Status}, ="Open")

60 - 89 Days

=COUNTIFS({Initial Escalation Date}, <=WORKDAY(TODAY(), -60), {Status}, ="Open")

90 Days or Greater

=COUNTIFS({Initial Escalation Date}, <=WORKDAY(TODAY(), -90), {Status}, ="Open")

Find more posts tagged with

Accepted answers

All comments

Leibel S

@Sherry Fox

See below example syntax for using the AND function:

=COUNTIFS({Initial Escalation Date}, AND(@cell>=WORKDAY(TODAY(), -60),@cell<=WORKDAY(TODAY(), -90)),{Status}, ="Open")

Hope this helps

Sherry Fox

@Leibel Shuchat ,

Unfortunately that did not work. I tried it for 60-90, anmd then edited it for 30-60. It resulted in 0 for the result on both

Leibel S

@Sherry Fox

I am not sure what you are trying to accomplish, i was showing you how to use the AND function.

What are your buckets based on?

Sherry Fox

@Leibel S ,

My formula is

=COUNTIFS({Initial Escalation Date}, AND(@cell >= WORKDAY(TODAY(), -30), @cell <= WORKDAY(TODAY(), -60)), {Status}, ="Open")

as you suggested (I changed the ranges for this bucket), and the result was 0. This is my Metrics sheet which calculates all counts from my main sheet, which is called "Escalations Sheet", where these fields are. Now, there is no end date, as I am calculating OPEN statuses.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.