Marking Oldest 5 Projects

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([email protected] < 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.
    Genevieve P. Employee Admin

    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:

    Let me know if you have any questions about this!