Formula using multiple criteria with dates and text to determine outcome and place in cell.

[jmendenhall1960]

Hello all,
Any help would be greatly appreciated.
Need a formula that will look at multiple cells to determine output.
In a nutshell I need to look to determine if my appointment times are exceeding time limits when I try to schedule.
This is code I am currently using for S2 =IF($D$1-R2>365,"over 1 yr","OK")

What I need know is more complex and way past my abilities, so any help would be greatly appreciated.
What I need to look at is this and not in any particular order.

If D2=I and L2=(”Carelink”,”Merlin”,Biotronic” or “Latitude” -any of these) and R2-365 >=D1 then “past due” in S2 otherwise “OK”

If D2=I and L2=(Blank or anything else) and R2-180 >=D1 then past due in S2 otherwise “OK”

If D2=P and L2=(”Carelink”,”Merlin”,Biotronic” or “Latitude” -any of these) and R2-560 >=D1 then “Past due” in S2 otherwise “OK”

If D2=P and L2=( Blank or anything else) and R2-365 >=D1 then “Past due” in S2 otherwise “OK”

Then IF there is anything other than a date in R2 then S2 leave blank

 

[Toadstool]

Hi JMendenhall1960,

Try
=IF(NOT(ISNUMBER(R2)),"",IF(AND(D2="I",ISNUMBER(SEARCH(L2,"Carelink/Merlin/Biotronic/Latitude"))),IF(R2-365>=$D$1,"Past due","OK"),IF(D2="I",IF(R2-180>=$D$1,"Past due","OK"),IF(AND(D2="P",ISNUMBER(SEARCH(L2,"Carelink/Merlin/Biotronic/Latitude"))),IF(R2-560>$D$1,"Past due","OK"),IF(AND(D2="P",R2-365>=$D$1),"Past due","OK")))))

 

[Toadstool]

JMendenhall1960,

A small change and here's my test harness.

JMendenhall1960.xlsx
	D								L						R	S	T	U
1	January 1, 2020														LS Clinic			Condition Test
2	I								Carelink						02-Jan-21	Past due		If D2=I and L2=(”Carelink”,”Merlin”,Biotronic” or “Latitude” -any of these) and R2-365 >=D1 then “past due” in S2 otherwise “OK”
3	I								Latitude						01-Jan-20	OK
4	I								dog						28-Jun-20	OK		If D2=I and L2=(Blank or anything else) and R2-180 >=D1 then past due in S2 otherwise “OK”
5	I														29-Jun-20	Past due
6	P								Merlin						15-Jul-21	Past due		If D2=P and L2=(”Carelink”,”Merlin”,Biotronic” or “Latitude” -any of these) and R2-560 >=D1 then “Past due” in S2 otherwise “OK”
7	P								Biotronic						14-Jul-21	OK
8	P								Goat						30-Dec-20	OK		If D2=P and L2=( Blank or anything else) and R2-365 >=D1 then “Past due” in S2 otherwise “OK”
9	P														31-Dec-20	Past due
10	I								Latitude									Then IF there is anything other than a date in R2 then S2 leave blank
11	P								Merlin
Sheet1 (2)

Cell Formulas
Range	Formula
S2:S11	S2	=IF(NOT(ISNUMBER(R2)),"",IF(AND(D2="I",L2<>"",ISNUMBER(SEARCH(L2,"Carelink/Merlin/Biotronic/Latitude"))),IF(R2-365>=$D$1,"Past due","OK"),IF(D2="I",IF(R2-180>=$D$1,"Past due","OK"),IF(AND(D2="P",L2<>"",ISNUMBER(SEARCH(L2,"Carelink/Merlin/Biotronic/Latitude"))),IF(R2-560>$D$1,"Past due","OK"),IF(AND(D2="P",R2-365>=$D$1),"Past due","OK")))))