If statement help

NadoDude

Board Regular
Joined
Jun 24, 2010
Messages
84
Need some major help from the Excel Gurus out there.

I am trying to write a formula in columns I and J that do the same thing as the formulas in columns G and H, but if the dates in col C or D are earlier than the date in col A, I want to use col A. If the dates in columns E and F are later than the date in col B, I want to use the date in col B.

Columns K and L show the results that I and J should display.

I have made numerous attempt to figure this out, but can't make it work.

<font size="2">Worksheet: Sheet1 UsedRange: $A$1:$L$11 Range: $A$1:$L$6</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">A</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">B</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">C</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">D</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">E</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">F</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">G</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">H</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">I</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">J</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">K</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">L</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cA1_705"></a><font size="2">Current FRP Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB1_705"></a><font size="2">Current FRP End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC1_705"></a><font size="2">Planned Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD1_705"></a><font size="2">Actual Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE1_705"></a><font size="2">Planned End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF1_705"></a><font size="2">Actual End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG1_705"></a><font size="2">Days Used</font></td><td width="100px" style="background-color:#ffffff;"><a name="cH1_705"></a><font size="2">Days Remaining</font></td><td width="100px" style="background-color:#ffffff;"><a name="cI1_705"></a><font size="2">Used This FRP</font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ1_705"></a><font size="2">Remaining this FRP</font></td><td width="100px" style="background-color:#ffffff;"><a name="cK1_705"></a><font size="2">COL I should equal</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL1_705"></a><font size="2">COL J should equal</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cA2_705"></a><font size="2">2/25/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB2_705"></a><font size="2">7/26/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC2_705"></a><font size="2">2/8/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD2_705"></a><font size="2">2/7/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE2_705"></a><font size="2">3/2/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF2_705"></a><font size="2">3/2/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG2_705"></a><font size="2"><a title="=IF(AND(D2="",F2=""),0,IF(F2="",TODAY()-D2,F2-D2))" href="#fG2_705">23</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cH2_705"></a><font size="2"><a title="=IF(AND(F2="",D2=""),E2-C2,IF(AND(F2="",D2<>""),E2-TODAY(),0))" href="#fH2_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK2_705"></a><font size="2">5</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL2_705"></a><font size="2">0</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><a name="cA3_705"></a><font size="2">2/25/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB3_705"></a><font size="2">7/26/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC3_705"></a><font size="2">4/3/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD3_705"></a><font size="2">4/3/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE3_705"></a><font size="2">6/7/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF3_705"></a><font size="2">6/7/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG3_705"></a><font size="2"><a title="=IF(AND(D3="",F3=""),0,IF(F3="",TODAY()-D3,F3-D3))" href="#fG3_705">65</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cH3_705"></a><font size="2"><a title="=IF(AND(F3="",D3=""),E3-C3,IF(AND(F3="",D3<>""),E3-TODAY(),0))" href="#fH3_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK3_705"></a><font size="2">65</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL3_705"></a><font size="2">0</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">4</font></td><td width="100px" style="background-color:#ffffff;"><a name="cA4_705"></a><font size="2">2/25/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB4_705"></a><font size="2">7/26/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC4_705"></a><font size="2">1/3/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD4_705"></a><font size="2">1/5/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE4_705"></a><font size="2">7/31/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG4_705"></a><font size="2"><a title="=IF(AND(D4="",F4=""),0,IF(F4="",TODAY()-D4,F4-D4))" href="#fG4_705">198</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cH4_705"></a><font size="2"><a title="=IF(AND(F4="",D4=""),E4-C4,IF(AND(F4="",D4<>""),E4-TODAY(),0))" href="#fH4_705">9</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK4_705"></a><font size="2">198</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL4_705"></a><font size="2">5</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">5</font></td><td width="100px" style="background-color:#ffffff;"><a name="cA5_705"></a><font size="2">2/25/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB5_705"></a><font size="2">7/26/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC5_705"></a><font size="2">7/22/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE5_705"></a><font size="2">7/25/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG5_705"></a><font size="2"><a title="=IF(AND(D5="",F5=""),0,IF(F5="",TODAY()-D5,F5-D5))" href="#fG5_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cH5_705"></a><font size="2"><a title="=IF(AND(F5="",D5=""),E5-C5,IF(AND(F5="",D5<>""),E5-TODAY(),0))" href="#fH5_705">3</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK5_705"></a><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL5_705"></a><font size="2">3</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">6</font></td><td width="100px" style="background-color:#ffffff;"><a name="cA6_705"></a><font size="2">2/25/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB6_705"></a><font size="2">7/26/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC6_705"></a><font size="2">7/22/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE6_705"></a><font size="2">8/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG6_705"></a><font size="2"><a title="=IF(AND(D6="",F6=""),0,IF(F6="",TODAY()-D6,F6-D6))" href="#fG6_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cH6_705"></a><font size="2"><a title="=IF(AND(F6="",D6=""),E6-C6,IF(AND(F6="",D6<>""),E6-TODAY(),0))" href="#fH6_705">10</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cI6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cJ6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cK6_705"></a><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><a name="cL6_705"></a><font size="2">5</font></td></table></div><p /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Address</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Value</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Formula</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fG2_705"></a><font size="2"><a href="#cG2_705">G2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">23</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(D2="",F2=""),0,IF(F2="",TODAY()-D2,F2-D2))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fH2_705"></a><font size="2"><a href="#cH2_705">H2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(F2="",D2=""),E2-C2,IF(AND(F2="",D2<>""),E2-TODAY(),0))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fG3_705"></a><font size="2"><a href="#cG3_705">G3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">65</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(D3="",F3=""),0,IF(F3="",TODAY()-D3,F3-D3))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fH3_705"></a><font size="2"><a href="#cH3_705">H3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(F3="",D3=""),E3-C3,IF(AND(F3="",D3<>""),E3-TODAY(),0))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fG4_705"></a><font size="2"><a href="#cG4_705">G4</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">198</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(D4="",F4=""),0,IF(F4="",TODAY()-D4,F4-D4))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fH4_705"></a><font size="2"><a href="#cH4_705">H4</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">9</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(F4="",D4=""),E4-C4,IF(AND(F4="",D4<>""),E4-TODAY(),0))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fG5_705"></a><font size="2"><a href="#cG5_705">G5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(D5="",F5=""),0,IF(F5="",TODAY()-D5,F5-D5))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fH5_705"></a><font size="2"><a href="#cH5_705">H5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(F5="",D5=""),E5-C5,IF(AND(F5="",D5<>""),E5-TODAY(),0))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fG6_705"></a><font size="2"><a href="#cG6_705">G6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(D6="",F6=""),0,IF(F6="",TODAY()-D6,F6-D6))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fH6_705"></a><font size="2"><a href="#cH6_705">H6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">10</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND(F6="",D6=""),E6-C6,IF(AND(F6="",D6<>""),E6-TODAY(),0))</font></td></tr></table></div>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,915
Members
430,244
Latest member
Ireland1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top