Find next date after today's date in a row but referencing a status

[P_Wood]

Hello there,

I have a matrix (RCI style) of data where there are 4 dates across each row that represent deadlines for that line item for the year.
Next to that date is a status (in progress, not started, complete).

I want to pull into one column for each item the next deadline that is based on:

• After todays date
• Status of complete (otherwise keep date before todays date)

Right now I have the min formula which is pulling the next date after todays date =MIN(IF(Q5:BD5>$B$1,Q5:BD5))

 

[DanteAmor]

Hi and welcome to MrExcel

Try:

=LET(a,Q5:BD5,b,MAX(IF((P5:BC5="Complete")*(a>$B$1),a)),c,MIN(IF(a>$B$1,a)),IF(b>0,b,SI(c>0,c,"NA")))

 

[Scott R]

=MAX( B$1, MINIFS( O5:BD5, O$2:BD$2, "Due date", OFFSET( O5:BD5,, -1 ), "<>Complete" ))

 

[P_Wood]

=MAX( B$1, MINIFS( O5:BD5, O$2:BD$2, "Due date", OFFSET( O5:BD5,, -1 ), "<>Complete" ))

Thank you!
This one worked perfectly