IF Statement (I think) Help Please

[mattyn]

I have a simple manpower spreadsheet, specific columns as follows:

Col F	Col I	Col O	Col U	Col X
Surname	Leaving Date	Gapped today + 7 days	Replacement Surname	Replacement arrival date
SMITH	12/2/21	here i want to indicate whether the role is gapped in seven days time. I think IF Statement - so GAP or OK	JONES	21/2/21

Like I say, in Col O i want to indicate whether the position is gapped in 7 days time.

I could have a name in Col F, with no date in Col I.
I cannot have a name in Col U without a date in Col X.

Criteria:
If incumbent name still in Col F, and Col I blank, Col O = "OK".
If Col F, I and X are blank, Col O = "GAP"
If Col F and I are blank, and Col X has a date before today + 7, Col O = "OK".
If Col F and I are blank, and Col X has a date after today + 7, Col O = "Gap".
If incumbent name in Col F, and date in Col I is less than today + 7, and Col X is blank, Col O = "GAP".

I have spent all afternoon trying to write the IF statement and keep getting myself tied up in knots. Hopefully its easy for one of you experts, and I appreciate you help and thank you in advance.

VMT

Matt

 

[steve the fish]

This would fit your criteria. Your example doesnt seem to be true for any of the criteria.

=IF(OR(AND(F2="",I2="",X2=""),AND(F2="",I2="",X2>TODAY()+7),AND(F2<>"",I2<TODAY()+7,X2="")),"GAP",IF(OR(AND(F2<>"",I2=""),AND(F2="",I2="",X2<TODAY()+7)),"OK","Something Else"))