Help with IF(NOT(AND)) Conditional Formatting Formula

[itsmekarak]

Hello All,

I'm working on a conditional format formula and have the first part figured out but stuck on the second part.

I need conditional formatting that says, =IF(NOT($B2=0),(AND($C2<B$4,$D2>$C2))) is true then add 1 month to the Due date (column E) and highlight that calculated month.

Therefore, in the following file (https://goo.gl/QrfDVg), you will see that in row 2 (Item 1), the March cell would highlight as I have manually show above.

And in row 4, (item 3), the April cell would highlight as I have manually show above.

I manually entered the formula into column F just trying to see if I could come up with the conditional formatting solution. I'm just stuck on the last part.

Any help you can give is greatly appreciated.


Thanks, Kara

 

[xyga1998]

Please have a look
https://www.sendspace.com/file/637jkx

I changed your formula to this =IF(AND($C2<B$4,$D2>$C2,$B2<>0),DATE(YEAR(E2),MONTH(E2)+1,DAY(E2))) ... As per my understanding for what you want this works as well as yours NOT($B2=0) equals AND($B2<>0) in TRUE or FALSE. If you wanted anything else please explain.
Inserted a new column F, and the highlighted cells take the formula using values in Column F (suit it according to your needs)

 

[itsmekarak]

Thank you so much! This is exactly what I needed. HUGE HELP!