Calculating Due Dates with Multiple Variables

[excelnewb02]

I'm writing a formula for a Due Date column with several variables. I've constructed a workable formula, but I'm wondering if there's a simpler more elegant way to accomplish what I want. I've attached a dummy version of the worksheet here. Let me see if I can explain the variables in plain English.

• Due date should be blank if column B is "Yes"
• If Column D is "Y", the Due date should be 59 days after the date in column C.
• If Column D is "N", the Due date should be 59 days after the later date between columns C and E.
• If there's a value in column F the Due Date should remain the 59 days after the later between C and E, even when column H is filled in.
• If there's a value in column G the Due Date should be blank until column H is filled in. Once column H has a value, the Due Date should be 59 days after the date in column H.
• If Column G> Column H, Due Date should revert to blank.

I think that's it, although it sounds simpler when I break it down like that then it did when I was building the worksheet. It's quite possible that I've left out a requirement that explains the ridiculous formula I ended up with:

=IF(B2="Y","",IF(H2<F2,H2+59,IF(G2>H2,"",IF(G2>F2,H2+59,IF(F2<>"",E2+59,IF(H2<>"",H2+59,IF(C2="","",IF(C2="n/a",E2+59,IF(G2<>"","",IF(D2="Y",C2+59,IF(E2="","",IF(E2>C2,E2+59,C2+59))))))))))))

Anyway that's why I'm here. Because that formula seemed unnecessarily complicated, and also required me to use conditional formatting to blank the cell when it produced an error. There must be a better way. Take a look at the attached which I think should make this explanation clearer.

[excelnewb02]

Just reworking this again and I realized the formula I had didn't totally work. Produced a blank when Column F had data when result should have been E2+59. Back to the drawing board but suggestions still appreciate.

[excelnewb02]

Ok, sorry for all the newbness with bad post title and flaws in my original formula. I've simplified and corrected and I think that I have it working properly now. Here's the formula along with an attachment so you can see it in action:

=IF(B2="Y","",IF(G2>H2,"",IF(G2<>"",H2+59,IF(C2="","",IF(C2="n/a",E2+59,IF(G2<>"","",IF(D2="Y",C2+59,IF(E2="","",IF(E2>C2,E2+59,C2+59)))))))))

My original question however remains. Is there an easier, more elegant way to achieve the same thing?

[excelnewb02]

Working on pairing down as I wait for responses. Notice I could get rid of one clause resulting in:


=IF(B2="Y","",IF(G2>H2,"",IF(G2<>"",H2+59,IF(C2="","",IF(C2="n/a",E2+59,IF(D2="Y",C2+59,IF(E2="","",IF(E2>C2,E2+59,C2+59))))))))

Doesn't change functionality at all. Also wondering how to successfully do an "AND" statement. For example, in the above, formula produces a bad result if you enter n/a in C2 until E2 is filled in. I could deal with this through conditional formatting, but I'd rather add an AND statement. i.e. IF(C2="n/a",AND(E2<>"",E2+59,IF(D2="Y",etc. But that produces a #VALUE error. Any tips?