Nested IF with Dates

[Charger_R_T]

I'm having issues figuring out how to get the formula to work....

I'm looking to place this formula in the status colume. I'll use row A19 as my example.
If A19 is blank, C19=blank
IF A19 has text, but no dates entered through E19:AV19, "Incomplete"
If E19:AV19 has a date over 365 days prior to today, "Expired". Even if there are dates within this row that are current or expiring soon.
If E19:AV19 has a date between 335-365 days prior to today, "Expires Soon". Even if there are dates within this row that are current.
IF E19:AV19 has all dates within 365 days prior to today, "Current"

Thanks for you help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(A19="","",IF(MIN(E19:AV19)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E19:AV19))=MIN(E19:AV19),"Expires soon","Current")))

 

[Charger_R_T]

Hi & welcome to MrExcel.
How about

=IF(A19="","",IF(MIN(E19:AV19)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E19:AV19))=MIN(E19:AV19),"Expires soon","Current")))

Almost there!! Just missing the below statement.

IF A19 has text, but no dates entered through E19:AV19, "Incomplete"

 

[Fluff]

Missed that bit, try

=IF(A19="","",IF(MIN(E19:AV19)=0,"Incomplete",IF(MIN(E19:AV19)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E19:AV19))=MIN(E19:AV19),"Expires soon","Current"))))

 

[Charger_R_T]

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Charger_R_T]

You're welcome & thanks for the feedback.

i've add some rows to the spreadsheet I'm using that requires some different information from the rows you provided a formula for before. Wondering if you can help with what I'm looking for? This would be in addition to what you have already provided.


Here is what you gave me before:
=IF(A15="","",IF(MIN(E15:AK15)=0,"Incomplete",IF(MIN(E15:AK15)<TODAY()-365,"Expired",IF(MEDIAN(TODAY()-365,TODAY()-335,MIN(E15:AK15))=MIN(E15:AK15),"Expires Soon","Safety Qualified"))))

Here is what I would like to add to this:
If AL15:AM115 and AR15:AS15, has a date over 1095 days prior to today, "Expired". Even if there are dates within this row that are current or expiring soon.
If AL15:AM15 and AR15:AS15, has a date over 1095 days prior to today, "Expired". Even if there are dates within this row that are current or expiring soon.

If AL15:AM15 and AR15:AS15 has a date between 1065-1095 days prior to today, "Expires Soon". Even if there are dates within this row that are current.
IF AL15:AM15 and AR15:AS15 has all dates within 1065 days prior to today, "Safety Qualified"




Row AN, AQ, AS will not be part of the formula.


If AO15:AP15 and AT15, is blank, C15=blank
IF AO15:AP15 and AT15, has text, but no dates entered through E19:AV19, "Incomplete"
If AO15:AP15 and AT15, has a date over 365 days prior to today, "Expired". Even if there are dates within this row that are current or expiring soon.
If AO15:AP15 and AT15, has a date between 335-365 days prior to today, "Expires Soon". Even if there are dates within this row that are current.
IFAO15:AP15 and AT15, has all dates within 335 days prior to today, "Safety Qualified"

 

[Charger_R_T]

You're welcome & thanks for the feedback.

here is a screen grab of the data...

 

[Fluff]

As this is now a significantly different question, it needs a new thread. Thanks