# 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")

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭

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.

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion