Marking Oldest 5 Projects

Options

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!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!