nested IF and AND statement

Occasional Contributor

Hello, I need assistance figuring out a formula for a table. its a bit complex. 

I need cell AE1 to calculate the lowest date between cells AB1 (Symptom onset date) and either cells  M1, P1, S1, V1, only if the Test result column for the corresponding test date is "positive". 

So If a person tests positive, I want the episode date Column to calculate the lowest date between that date and the symptom onset date (Note symptom onset can sometimes be blank, so then the test date would be the "episode date)

Capture.PNG

 

2 Replies

@jaolvera 

=IF(AB1="","",LET(d,DATE(9999,12,31),MIN(AB1,IF(O1="Positive",M1,d),IF(R1="Positive",P1,d),IF(U1="Positive",S1,d),IF(X1="Positive",V1,d))))

@jaolvera 

From your PM I understand you want

 

=LET(d,DATE(9999,12,31),MIN(IF(AB1<>"",AB1,d),IF(O1="Positive",M1,d),IF(R1="Positive",P1,d),IF(U1="Positive",S1,d),IF(X1="Positive",V1,d)))