if then statement

[excelnotguru]

<TABLE style="WIDTH: 928pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1235><COLGROUP><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9325" width=255><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" span=2 width=50><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 191pt; HEIGHT: 21pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl133 height=28 width=255>Tenant</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 38pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl133 width=50>SF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 38pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl133 width=50>%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl134 width=64>Expiration Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=75>8/1/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 58pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=77>8/1/2013</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=73>8/1/2014</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=72>8/1/2015</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 60pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=80>8/1/2016</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=79>8/1/2017</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=71>8/1/2018</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=72>8/1/2019</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=70>8/1/2020</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=74>8/1/2021</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl139 width=73>8/1/2022</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136 height=14></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl137 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: navy; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl136></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl138 height=14>Anchor Tenant</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl131> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl135> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt dotted" class=xl130> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl129> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl129> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl129> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl129> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl129> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128> </TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl132 height=14>Tenant1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl131>57,541</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl135>66%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt dotted" class=xl130>10/31/21</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>57,541</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128>0</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 928pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1235><COLGROUP><COL style="WIDTH: 191pt; mso-width-source: userset; mso-width-alt: 9325" width=255><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" span=2 width=50><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 191pt; HEIGHT: 10.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl131 height=14 width=255>Tenant2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; WIDTH: 38pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl130 width=50>1,109</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ccffff; WIDTH: 38pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl132 width=50>1%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt dotted" class=xl129 width=64>12/31/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=75>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 58pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=77>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=73>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=72>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 60pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=80>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=79>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=71>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=72>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=70>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=74>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl128 width=73>0</TD></TR></TBODY></TABLE>


Considering the following, how can I create a formula to drag from 8/1/2012 to 8/1/2022 to show the zeros as the lease does not expire during certain years and show 57,541 as shown above during the year in which the lease expires. Also, some of the leases expire during the current year 2011. That is what is giving me an issue as my if then statement is not recognizing a lease as expiring as it is not in the range I set. I probably made the formula too complicated anyway and could likely use a simpler formula. Please help.

Current if then =IF(AND($E8<=G$5,$E8>F$5),$C8,0)
Any insight is greatly appreciated.

 

[Rastaman]

How about something like this:

=IF(YEAR(F$5)>=YEAR($E8),$C8,0)

Pasted into cell F8 (if I got your cell locations correct) and copied across down/across.


Rick

 

[ukmikeb]

Hi

Try in F8 -

=IF(COLUMNS($F$5:F$5)<>1,IF(AND(F$5>=$E8,E$5<$E8),$C8,0),IF($E8<=$F$5,$C8,0))

copied across and down.

hth

@Rastaman - What about the months and days?

 

[Rastaman]

I could be wrong, but the requestor asked to capture in what year the lease expires. In the original example for tenant1 the expiration date is October but the value was still filled in for the 8/1/2011 cell.

In looking at the original table a little more closely, if the desire is to only show the SF value in the year that the lease expires, my formula would change to this:

=IF(YEAR(F$5)=YEAR($E8),$C8,0)


Rick