nested if with and statements

[deecarrion]

Hi! I need help.. I have a table that on cell f4 is a due date, g4 is the completed date, a1 is todays date (today func) , now in j4 i want it to read wether the project was complete, past due and if no due date was selected i need j4 to remain blank.

Summary..
If f4 is blank = j4 blank
If g4 has text = j4 says completed
If g4 is blank AND f4>a1 = j4 says past due
Heres my issue... Also need.. If g4 is blank AND f4<a1 = j4 remains blank.

Tried this and if i dont get errora, i wont get the results i want or excel tells me that i have too much request in the same line.. This is what i got so far but iys not working..

Cant get j4 to remain blank when f4 is smaller than a1

=IF(AND((ISBLANK(G4)),F4<$a$1,NOT(ISBLANK(F4))),"PastDue", IF((ISBLANK(F4)),"","Complete"))

 

[jtakw]

Hi! I need help.. I have a table that on cell f4 is a due date, g4 is the completed date, a1 is todays date (today func) , now in j4 i want it to read wether the project was complete, past due and if no due date was selected i need j4 to remain blank.

Summary..
If f4 is blank = j4 blank
If g4 has text = j4 says completed
If g4 is blank AND f4>a1 = j4 says past due
Heres my issue... Also need.. If g4 is blank AND f4< a1 = j4 remains blank.

Tried this and if i dont get errora, i wont get the results i want or excel tells me that i have too much request in the same line.. This is what i got so far but iys not working..

Cant get j4 to remain blank when f4 is smaller than a1

=IF(AND((ISBLANK(G4)),F4<$a$1,NOT(ISBLANK(F4))),"PastDue", IF((ISBLANK(F4)),"","Complete"))

Hi,

The logic in your description seem incorrect, above in red, I believe the condition should be reversed.

"If g4 is blank AND f4>a1 = j4 says past due" should be BLANK, not Past Due, since F4 (due date) is greater than, meaning later than A1 (today), meaning not yet due.
"If g4 is blank AND f4< a1 = j4 remains blank" should be Past Due, not BLANK, since F4 (due date) is less than, meaning earlier than A1 (today).

Considering the above, use this:

=IF(OR(F4="",AND(G4="",F4>A1)),"",IF(G4<>"","Completed","Past Due"))

Let me know if this works for you.

 

[jtakw]

Hi,

The logic in your description seem incorrect, above in red, I believe the condition should be reversed.

"If g4 is blank AND f4>a1 = j4 says past due" should be BLANK, not Past Due, since F4 (due date) is greater than, meaning later than A1 (today), meaning not yet due.
"If g4 is blank AND f4< a1 = j4 remains blank" should be Past Due, not BLANK, since F4 (due date) is less than, meaning earlier than A1 (today).

Considering the above, use this:

=IF(OR(F4="",AND(G4="",F4>A1)),"",IF(G4<>"","Completed","Past Due"))

Let me know if this works for you.

Hi,

Too late to edit my last post, I modified the formula so that if the Due Date is Today, it'll remain BLANK, and only show "Past Due" if Due Date is earlier than Today, all other conditions remain the same as my last post. Take your pick.

=IF(OR(F4="",AND(G4="",F4>=A1)),"",IF(G4<>"","Completed","Past Due"))

 

[deecarrion]

Hi,

Too late to edit my last post, I modified the formula so that if the Due Date is Today, it'll remain BLANK, and only show "Past Due" if Due Date is earlier than Today, all other conditions remain the same as my last post. Take your pick.

=IF(OR(F4="",AND(G4="",F4>=A1)),"",IF(G4<>"","Completed","Past Due"))

Thanks for noticing i messed up the logic... Im so tired of looking at this issue. I'm not home but once i get there i will test shaft you said. I really appreciate it!!! I need to get better with excel..if you have any advice on books or training aids please let me know. Once i test this i will Reply again.