IF Statement with a BUT

[mattyn]

Hi all

Currently have a simple IF statement in B6 that says If A6=Yes, then do the next If statement which is if the date in C6 is less than today + 30, return "GAP" otherwise return "OK: therefore:

=IF(A6="Yes",IF(C6<Today()+30,"GAP"."OK"),"OK")

But, I have another date in F6. If F6 date is less than today + 30, I need the return above to be "OK", if the F6 date is greater than 30, I need the return above to be "GAP". Also If F6 is blank, I need F6 to be ignored.

I just cannot see the logical question - and therefore the formula.

Confused

Matt

 

[joeu2004]

@mattyn, perhaps the following will do:

=IF(A6<>"yes", "ok",
IF(AND(C6<>"",C6<TODAY()), "gap",
IF(AND(F6<>"",F6>TODAY()), "gap", "ok")))

The issue is: you did not say under what conditions you want to look at F6.

For example, do you want to look at F6 only if C6>=TODAY() is false, as I did; or do you want to look at F6 only if C6<TODAY() is true?

And do you only want to look F6 when A6="yes", as you did with C6 -- which is what I assume?

By the way, the opposite of "greater than" is not "less than". Technically, you did not say what you want when C6=TODAY() or F6=TODAY().

Bottom line: with 3 variables and two(?) states each, there are 8 possible combinations of conditions. You might need to test them all to be sure any formula works for your.

 

[mattyn]

Thank you for the response - need to really be specific rather than generalise. I really appreciate your help as the logic part here is screwing with my head! It could be age!

Anyway - ignore my initial post please. The required formula needs to be in U5 and V5.

I5 is the date we expect an employee to leave. Format dd-mmm-yy

J5 is confirmation whether the employee leaves on that date – either “Yes” or “No”.

AC5 is the date we are expecting a new employee in for that post. Format dd-mmm-yy

U5 – needs to indicate whether the employee position is gapped (i.e. no employee in post) today. So either “GAP” or “OK”.

V5 - needs to indicate whether the employee position is gapped (i.e. no employee in post) in 7 days time. So either “GAP” or “OK”.

***
If J5 states “No”, and I5 states any date, U5 and V5 must say “OK”.

If J5 states “Yes”, and I5 states a date before today, U5 must state “GAP”. V5 must also state “GAP” unless AC5 states a date within 7 days of today in which case V5 should read “OK”. Should AC5 state a date beyond 7 days from today, or is blank, V5 should read “GAP”.

If J5 states “YES” and I5 states a date between today and 7 days’ time U5 should read “OK”, unless AC5 reads a date within 7 days time. V5 should read GAP unless AC5 states a date within 7 days of today.

J5	I5	AC5	U5	V5
No	Any date	Blank or any date	OK	OK
Yes	Before today	Within 7 days of today	Gap	OK
Yes	Before today	Blank	Gap	GAP
Yes	Within 7 days of today	Within 7 days of today	OK	OK
Yes	Within 7 days of today	Blank	OK	GAP
Yes	After 7 days of today	Within 7 days of today	OK	OK
Yes	After 7 days of today	Blank	OK	OK
Yes	After 7 days of today	After 7 days of today	OK	OK

 

[joeu2004]

I assume that "within 7 days of today" means: between today and today+7 inclusively.

Specifically, it does not include any dates "before today", notably between today-7 and today inclusively.

Right?

If J5 states “Yes”, and I5 states a date before today, [....] V5 must also state “GAP” unless AC5 states a date within 7 days of today in which case V5 should read “OK”. Should AC5 state a date beyond 7 days from today, or is blank, V5 should read “GAP”.

The two statements are not consistent. Specifically, what if AC5 is "before today"?

The first statement suggests that V5 should display "GAP" because AC5 is not "a date within 7 days of today".

The second statement suggests that V5 should display "OK" because AC5 is not blank and AC5 is not "beyond 7 days from today".

I assume the first statement is what you intended, namely: V5 should display "OK" only if AC5 is between today and today+7 inclusive.

If J5 states “YES” and I5 states a date between today and 7 days’ time U5 should read “OK”, unless AC5 reads a date within 7 days time.
[....]

J5	I5	AC5	U5	V5
Yes	Within 7 days of today	Within 7 days of today	OK	OK
Yes	Within 7 days of today	Blank	OK	GAP

And what should U5 display if AC5 is "a date within 7 days" of today?!

Your example shows that U5 should display "OK", even in that case.

-----

I think the following demonstrates what you intended. You should test with additional cases that are not included in your posted example.

mattyn gap or ok.xlsx
	A	B							I	J											U	V							AC
1	today	6/19/2020
5									6/26/2020	No											OK	OK
6									6/18/2020	Yes											GAP	OK							6/26/2020
7									6/18/2020	Yes											GAP	GAP
8									6/26/2020	Yes											OK	OK							6/26/2020
9									6/26/2020	Yes											OK	GAP
10									6/27/2020	Yes											OK	OK							6/26/2020
11									6/27/2020	Yes											OK	OK
12									6/27/2020	Yes											OK	OK							6/27/2020
Sheet1

Formulas:
U5: =IF(AND(J5="yes", I5<>"", I5<$B$1), "GAP", "OK")
V5: =IF(AND(J5="yes", I5<>"", I5<=$B$1+7, OR(AC5<$B$1, $B$1+7<AC5)), "GAP", "OK")

I test I5<>"" because Excel interprets I5 as zero if it is empty, and I5<B1 would be true, which is not the intention.

OTOH, we do not need to test AC5<>"" because: (a) if AC5 is empty, AC5<B1 would be true, as intended; and (b) if AC5 is the null string (""), B1+7<AC5 would be true, as intended, because Excel treats any string as greater than any numerical value.

 

[mattyn]

Really many thanks for coming up with this. Had a slow Friday afternoon so have solved it by adding "stages" on the hidden Data worksheet - rather than the clever and rather ingenious formula you generate above.
I will however look at this closer later in the week to see if it becomes slicker. I think yours will do the job so do appreciate the assistance - I will report back toward the end of the week.

Many thanks again. Much appreciated.

Matt