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>
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,680
Messages
5,512,825
Members
408,916
Latest member
juliesmithing

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top