Logic help with nested IF statements

[itfmm]

Hey guys,

Just need some help with nesting several IF statements to calculate how much time has passed between two dates. I'm working with data that experienced a shift in how fields were being used in the program, so older data is missing the current field and instead utilizes another date. Here are the fields:

Column A: docs received
Column B: client intent to proceed
Column C: VOE ordered

At this point, I have two different functions that need to somehow be nested in order to utilize just one function for the calculation.

Here's the first function that uses an older field to replace the missing new field: =IF(AND(B2 = "",A2 <> ""),A2,IF(B2 <> "", B2, "")) <-- if "client intent to proceed" is blank and "docs received" is NOT blank, then use date in field "docs received"; otherwise if "client intent to proceed" is NOT blank, use that date, otherwise leave it blank

Here's the second function where calculation between dates occurs: =IF(B2= "" ,"" ,IF(C2-B2 < 0, "" ,(C2-B2))) <--if "client intent" is blank, then leave blank, if "VOE ordered" minus "client intent" is less than zero (client intent should not precede VOE order date), then leave as blank; otherwise, "VOE ordered" minus "client intent to proceed"

So looking to nest the first function within the second - any help appreciated!

Thank so much!

 

[itfmm]

Figured it out!

=IF(C2-(IF(AND(B2="",A2 <> ""),A2,IF(B2 <> "",B2, "")))<0,"",C2-(IF(AND(B2= "",A2 <> ""),A2,IF(B2 <> "",B2, ""))))

 

[RCBricker]

Hey guys,

Just need some help with nesting several IF statements to calculate how much time has passed between two dates. I'm working with data that experienced a shift in how fields were being used in the program, so older data is missing the current field and instead utilizes another date. Here are the fields:

Column A: docs received
Column B: client intent to proceed
Column C: VOE ordered

At this point, I have two different functions that need to somehow be nested in order to utilize just one function for the calculation.

Here's the first function that uses an older field to replace the missing new field: =IF(AND(B2 = "",A2 <> ""),A2,IF(B2 <> "", B2, "")) <-- if "client intent to proceed" is blank and "docs received" is NOT blank, then use date in field "docs received"; otherwise if "client intent to proceed" is NOT blank, use that date, otherwise leave it blank

Here's the second function where calculation between dates occurs: =IF(B2= "" ,"" ,IF(C2-B2 < 0, "" ,(C2-B2))) <--if "client intent" is blank, then leave blank, if "VOE ordered" minus "client intent" is less than zero (client intent should not precede VOE order date), then leave as blank; otherwise, "VOE ordered" minus "client intent to proceed"

So looking to nest the first function within the second - any help appreciated!

Thank so much!

This should work

=IF(AND(B11="",A11=""),"",IF(OR(AND(B11>C11,A11=""),AND(A11>C11,B11="")),"",IF(B11="",C11-A11,C11-B11)))

 

[itfmm]

That would work except there are instances where both A2 and B2 have a date, but there isn't one in C2. Your function then returns a negative 42k number. I ended up going with this which accounts for when all cells are blank.

=IFERROR(IF(C2-(IF(AND(B2="",A2<>""),A2,IF(B2<>"",B2,"")))<0,"",C2-(IF(AND(B2="",A2<>""),A2,IF(B2<>"",B2,"")))),"")

I'd prefer to not use the IFERROR patch, so if you have a suggestion on how to amend the function to remove it, please let me know. Thank you!