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(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!
Help Article Resources
Check out the Formula Handbook template!