How to isolate the next due date from a list of dates in Google Sheets?

Question

I have a spreadsheet with a list of tasks and due dates in Google Sheets (Column E). I have an adjacent column which tracks the status of the task as either "Pending", "In Progress", or "Complete" (Column F).

What I'd like to do is have the next "Pending" or "In-Progress" date appear on the top corner of the spreadsheet so that I know as soon as I open the sheet which due date is upcoming.

Is there an "IF" statement I can use here? Or other statement that makes this possible?

Any input is appreciated. Thank you in advance!

Answer 1

Place this formula in a position where there are 4 free cells (2x2 matrix)

={"Pending","In Progress";min(filter(E2:E,F2:F="Pending")),min(filter(E2:E,F2:F="In Progress"))}

You will get something like this:

Isolating the date with a lower value among those with the word "Pending", you will find the most urgent work date (and so also for "in Progress"). If, on the other hand, you want to keep under control the last "Pending" (or "in Progress") change the min() function with the max() function