Help with simple formula

NadoDude

Board Regular
Joined
Jun 24, 2010
Messages
84
For the formula in cell T2, I want to add something to it that says if P2 is blank, then use T1+1

<font size="2">Worksheet: Assignments UsedRange: $A$1:$W$2641 Range: $L$1:$T$2</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">L</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">M</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">N</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">O</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">P</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Q</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">R</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">S</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">T</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="cL1_705"></a><font size="2">Report Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM1_705"></a><font size="2">PRD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN1_705"></a><font size="2">EDD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO1_705"></a><font size="2">TERM LV Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP1_705"></a><font size="2">Detach Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ1_705"></a><font size="2">Detach Reason</font></td><td width="100px" style="background-color:#ffffff;"><a name="cR1_705"></a><font size="2">Transfer to</font></td><td width="100px" style="background-color:#ffffff;"><a name="cS1_705"></a><font size="2">Notes</font></td><td width="100px" style="background-color:#ffffff;"><a name="cT1_705"></a><font size="2">7/26/2010</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="cL2_705"></a><font size="2">11/26/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM2_705"></a><font size="2">6/1/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT2_705"></a><font size="2"><a title="=IF(AND($L2<=T$1,$P2>T$1),1,"")" href="#fT2_705"></a></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="fT2_705"></a><font size="2"><a href="#cT2_705">T2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND($L2<=T$1,$P2>T$1),1,"")</font></td></tr></table></div>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
???
=if(isblank(P2),T1+1,IF(AND($L2<=T$1,$P2>T$1),1,""))
 
Upvote 0
Maybe this:

=IF($P2="",T1+1,IF(AND($L2<=T$1,$P2>T$1),1,""))

=IF($P2="",T$1+1,IF(AND($L2<=T$1,$P2>T$1),1,""))

??

What is the hierarchy of the criteria? Does the AND($L2<=T$1,$P2>T$1) come before the $P2="" or is it the reverse?
 
Upvote 0
Maybe give us an example of two of what the results should look like.
 
Upvote 0
Columns T, U, and V show if the person was attached on the day shown in the heading indicated by a 1. Looking at the worksheet below, the second person arrived on 4/24/2004 and retired on 7/28/2010, and the results in T, U and V show that he was there on 7/26 and 7/27, but not there on 7/29.

The first person arrived on 11/26/2008, and has not left, meaning there should be a 1 in columns T, U and V since P is blank and does not have a date greater than the heading dates.

<font size="2">Worksheet: Assignments (2) UsedRange: $A$1:$W$2640 Range: $L$1:$V$3</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">L</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">M</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">N</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">O</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">P</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Q</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">R</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">S</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">T</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">U</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">V</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="cL1_705"></a><font size="2">Report Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM1_705"></a><font size="2">PRD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN1_705"></a><font size="2">EDD</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO1_705"></a><font size="2">TERM LV Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP1_705"></a><font size="2">Detach Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ1_705"></a><font size="2">Detach Reason</font></td><td width="100px" style="background-color:#ffffff;"><a name="cR1_705"></a><font size="2">Transfer to</font></td><td width="100px" style="background-color:#ffffff;"><a name="cS1_705"></a><font size="2">Notes</font></td><td width="100px" style="background-color:#ffffff;"><a name="cT1_705"></a><font size="2">7/26/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cU1_705"></a><font size="2">7/27/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cV1_705"></a><font size="2">7/28/2010</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="cL2_705"></a><font size="2">11/26/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM2_705"></a><font size="2">6/1/2012</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT2_705"></a><font size="2"><a title="=IF(AND($L2<=T$1,$P2>T$1),1,"")" href="#fT2_705"></a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU2_705"></a><font size="2"><a title="=IF(AND($L2<=U$1,$P2>U$1),1,"")" href="#fU2_705"></a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cV2_705"></a><font size="2"><a title="=IF(AND($L2<=V$1,$P2>V$1),1,"")" href="#fV2_705"></a></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="cL3_705"></a><font size="2">4/2/2004</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM3_705"></a><font size="2">1/31/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN3_705"></a><font size="2">8/1/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cP3_705"></a><font size="2">7/28/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ3_705"></a><font size="2">Retirement</font></td><td width="100px" style="background-color:#ffffff;"><a name="cR3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cT3_705"></a><font size="2"><a title="=IF(AND($L3<=T$1,$P3>T$1),1,"")" href="#fT3_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cU3_705"></a><font size="2"><a title="=IF(AND($L3<=U$1,$P3>U$1),1,"")" href="#fU3_705">1</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cV3_705"></a><font size="2"><a title="=IF(AND($L3<=V$1,$P3>V$1),1,"")" href="#fV3_705"></a></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="fT2_705"></a><font size="2"><a href="#cT2_705">T2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND($L2<=T$1,$P2>T$1),1,"")</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fU2_705"></a><font size="2"><a href="#cU2_705">U2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND($L2<=U$1,$P2>U$1),1,"")</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fV2_705"></a><font size="2"><a href="#cV2_705">V2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND($L2<=V$1,$P2>V$1),1,"")</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fT3_705"></a><font size="2"><a href="#cT3_705">T3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND($L3<=T$1,$P3>T$1),1,"")</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fU3_705"></a><font size="2"><a href="#cU3_705">U3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND($L3<=U$1,$P3>U$1),1,"")</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fV3_705"></a><font size="2"><a href="#cV3_705">V3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(AND($L3<=V$1,$P3>V$1),1,"")</font></td></tr></table></div>
 
Upvote 0
How about this:

=IF($P2="","",IF(AND(T$1>=$L2,T$1<$P2),1,""))
 
Upvote 0
That puts tomorrows date in the cell not the 1
well i'm quite aware of what it puts, but after you fail to explain clearly what you want to achieve, all that's left for the ones that try to help is guessing exactly what you have in mind.

so just try to make it clear to the others when post ...
 
Upvote 0
Mike,
Your solution did not work, however it made me think of something.

=IF($P2="",IF(AND($L2<=T$1,T$1+1>T$1),1,""),IF(AND($L2<=T$1,$P2>T$1),1,""))

I tried it and it seems to be putting the 1s in all the right places. Thanks for making my brain look for other solutions.

bobsan42 - I tried to explain my problem the best way I knew how. If I offended you in some way, please accept my apologizes.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top