How can I identify the earliest date for each of 5 criteria in another column?

Harper141

Hopefully I can explain this and it make sense! I’ve done some googling and tried various things but don’t feel like I’m on the right path…

I have a sheet with multiple columns of data, including a Manager column (Contact List) and Date of Last Update (Date) column. The Date of Last Update is updated via workflow when a new comment is added to the row (regardless of modifications to any other columns on the row).

Similar to a MAX Helper column, I want to figure out how to find the *earliest* date under Date of Last Update, by Manager. (There are 5 managers total). I envision this formula would display the managers name on the row where they are indicated in the Manager column AND the Date of Last Update is the oldest date compared to the others they are assigned to.

Then, my idea is to create a report filtered by a specific Manager and this new formula column with their name, and the report would display the “Date of Last Update” column. Ultimately this report would only display a single row/column, which I can then include as a report widget on a dashboard as an indicator of the oldest updated item they’re assigned to. This would ensure that the managers can, at a glance, check to see when their updates were last completed and not accidentally forgotten a single row across multiple sheets. There are 4 separate sheets where I would add this column, so that on the dashboard, there is quick visibility for that manager to see how old their updates are.

How do I even begin assembling this formula? Is there a better way to go about this than how I am approaching, perhaps through Summary fields on each sheet?

Davin Vo

Hello @Harper141

From your description, I believe you're looking for a MIN(COLLECT()) Function. You'd find the MIN (earliest update date), using the COLLECT to break down the criteria, (Manager Name).

You can either do a summary field or additional columns. Here's just a general way you can write it and alter it as needed. Same thing will work for MAX if you're looking for the latest update

=MIN(COLLECT([Date of Last Update]:[Date of Last Update], Manager:Manager, [Manager (Report)]@row))

You can sub out the [Manager (Report)]@row with a name or reference their name in another cell.

Hope this helps!

Harper141

This helped tremendously! THANK YOU!!