Forum Discussion
2 IF Conditions with DATEDIF forumula, returns number even if conditions aren't met
I am receiving chapters from authors, sending them to editors, and getting them back from editors.
Need to count the # of days a chapter has been with an editor, but not if the chapter has been returned. And under other conditions I want to return text.
I got that part to work (took me 2 hours) but trying to nest the conditions to return specific text has been a flop.
IF(AND(ISNUMBER(A2),ISNUMBER(B2),ISBLANK(C2)),DATEDIF(B2,TODAY(),"d"),"")
Thought? Advice?
| Date Received | Date Sent on to editor | Date Returned | # Days Pending | IF(AND(ISNUMBER(A2),ISNUMBER(B2),ISBLANK(C2)),DATEDIF(B2,TODAY(),"d"),"") |
| 4/10/18 | 5/1/18 | 5/5/18 | wish it would say say "complete" | |
| 5/10/18 | wish it would say say "not to editor" | |||
| notes here, like they said it would be late | ||||
| 4/10/18 | 5/1/18 | 31 | ||
| wish it would say say "not in" | ||||
| A - Date Received | B - Date Sent on to editor | C - Date Returned | Return this: | |
| If A is blank or a note, not a number | and B is blank or a note, not a number | and C is blank or a note, not a number | not in | |
| if A is a date | and B is blank or a note, not a number | and C is blank or a note, not a number | not to editor | |
| if A is a date | and if B is a date | and C is blank or a note, not a number | DATEDIF of B from TODAY, in Days | |
| if A is a date | and if B is a date | and if C is a date | completed |
Hi Leah,
Please try this formula:
=IF(AND(ISNUMBER(C2),ISNUMBER(B2),ISNUMBER(A2)),"complete",
IF(AND(ISNUMBER(A2),B2="",C2=""),"not to editor",
IF(AND(A2="",B2="",C2=""),"not in",
IF(AND(ISNUMBER(A2),ISNUMBER(B2)),DATEDIF(B2,TODAY(),"d"),""))))And find it in the attached file.
Regards
Hi Leah,
Please try this formula:
=IF(AND(ISNUMBER(C2),ISNUMBER(B2),ISNUMBER(A2)),"complete",
IF(AND(ISNUMBER(A2),B2="",C2=""),"not to editor",
IF(AND(A2="",B2="",C2=""),"not in",
IF(AND(ISNUMBER(A2),ISNUMBER(B2)),DATEDIF(B2,TODAY(),"d"),""))))And find it in the attached file.
Regards
Thank you Haytham!
Your solution works exactly like I hoped.
