Marking Oldest 5 Projects

Fiona Painter

Hello! I have been working with some data and I want to eventually be able to pull a report of my "past due" projects and show the oldest 5 projects. To do this, I have created a helper column in my source sheet that using the formula:

=IF(Date@row < SMALL(Date:Date, 6), 1, 0) in order to flag the 5 oldest dates in my due date column.

This works, however it is flagging dates from even my completed projects when I only want it to flag ones that are past due (they are marked as red in my example). I then tried using the AND function in addition to the above formula, like so:

=IF(AND(Red2 = "red", ([Due Date]2 < SMALL([Due Date]:[Due Date], 6))), 1, 0)

and while it does work, it only flage 3 dates as 2 of the oldest dates are marked as complete.

Here is a screenshot of what happens on my test sheet.

Is there a way to only identify the oldest 5 dates that are not marked as complete?

Thanks in advance!

Genevieve P.

Hi Fiona,

Instead of using the AND function, we can use COLLECT embedded within SMALL to specify a certain criteria... try this:

=IF([Due Date]@row < SMALL(COLLECT([Due Date]:[Due Date], Red:Red, "Red"), 6), 1, 0)

Here's more information about the COLLECT Function: https://help.smartsheet.com/function/collect

Let me know if you have any questions about this!

Cheers,

Genevieve