How do I pull the oldest date for a tracker based on specific statuses?

Von Doss

I'm completely stumped on what to do because everything I've tried is coming back invalid or unparseable. Below is the scenario:

Building a Sheet to track the total # of Requests and Issues by Sales Rep in my organization. In addition, I'd like to be able to pull the oldest date of an open request at the same time.

Date Entered = The date the Rep entered the issue or request.

Status (Options) = Completed, Pending, In Progress, Not Started

I only want to track Issues/Requests with the Status - In Progress and Not Started

Thank you for your help!!

Sameer Karkhanis

It will be also great if you can share the format of the master sheet.

As an alternative you can also create a report, filter on the statuses you want, group by Sales Rep and summarize by Count. Could be easier than using formulas.

Danielle Arteaga

Hi, Von. I'm not sure I completely follow the structure of your sheet, but I think I can get you part of the way there.

I recreated your sheet with some example data, like so:

I think you intend on having the oldest dates appear in the columns of your sheet, but that won't really work with the formula, so I placed it in a sheet summary (where you can see the formula I used). You could also do this on an entirely separate sheet, where you list each Sales Rep only once, then place the MIN/COLLECT formula next to each sales rep's name to identify the oldest request and oldest issue for each rep. (You would use cross-sheet references for this, but the formula structure would be largely the same.)

NOTE: I cannot figure out how to have this formula work with a "Status" value that is either "Not Started" or "In Progress." I will update if I figure out a solution. @Paul Newcome might be of assistance here, too. He's fabulous with INDEX/COLLECT type of functions. :-)

Danielle Arteaga

Possibly got you the rest of the way there using a "helper" sheet. On this sheet, each Sales Rep is listed only once. The columns run calculations to find the oldest date for each request and each issue for each sales rep. The last two columns then compare the "Not Started" and "In Progress" dates to determine which one is oldest. You can then use "cell link" to pull the Oldest Request date and Oldest Issue date back to the main sheet.

To calculate the oldest request that is not started:

=MIN(COLLECT({Oldest Date Example Date of Request}, {Oldest Date Example Sales Rep}, [Sales Rep]@row, {Oldest Date Example Status}, "Not Started"))

To calculate the oldest request that is in progress:

=MIN(COLLECT({Oldest Date Example Date of Request}, {Oldest Date Example Sales Rep}, [Sales Rep]@row, {Oldest Date Example Status}, "In Progress"))

To calculate the oldest issue that is not started:

=MIN(COLLECT({Oldest Date Example Date of Issue}, {Oldest Date Example Sales Rep}, [Sales Rep]@row, {Oldest Date Example Status}, "Not Started"))

To calculate the oldest issue that is in progress:

=MIN(COLLECT({Oldest Date Example Date of Issue}, {Oldest Date Example Sales Rep}, [Sales Rep]@row, {Oldest Date Example Status}, "In Progress"))

To find the oldest date for requests:

=MIN([Oldest Request - Not Started]@row:[Oldest Request - In Progress]@row)

To find the oldest date for issues:

=MIN([Oldest Issue - Not Started]@row:[Oldest Issue - In Progress]@row)

You can then use cell link to pull the values from "Oldest Request" and "Oldest Issue" back into your main sheet:

If your Sales Reps can have multiple entries on your tracking sheet, you may want to consider setting up a PARENT/CHILD structure so that each sales rep's entries are collected as children under their respective parents. That may make your entire structure a bit easier to manage and avoid the need for a second sheet.