Search Date for Year where Date is Stored in Varying Text Format

[108monkeys]

Here is a formula which I am using to identify if a date is within a financial year from a date stored as text with varying formats and lengths.

<TABLE style="WIDTH: 855pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1139 border=0 x:str><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 599pt; mso-width-source: userset; mso-width-alt: 29184" width=798><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 23pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=30 height=17> </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=63>A</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 115pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=153>B</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=95>C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 599pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=798>D</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">YEAR</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">DATE AS TEXT</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">RETURNS</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">FORMULA USES THE YEAR AND THE DATE WHICH IS STORED AS TEXT => SHOULD RETURN TRUE OR FALSE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>2</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2010</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1/09/2010 13:47:18.867</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:fmla="=OR(AND(1*RIGHT(LEFT(C3,10),4)=B3,1*RIGHT(LEFT(C3,5),2)>6),AND(1*RIGHT(LEFT(C3,10),4)=B3+1,1*RIGHT(LEFT(C3,5),2)<7))" x:err="#VALUE!">#VALUE! </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C2,10),4)=B2,1*RIGHT(LEFT(C2,5),2)>6),AND(1*RIGHT(LEFT(C2,10),4)=B2+1,1*RIGHT(LEFT(C2,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C2,10),4)=B2,1*RIGHT(LEFT(C2,5),2)>6),AND(1*RIGHT(LEFT(C2,10),4)=B2+1,1*RIGHT(LEFT(C2,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>3</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1/09/2010 13:47:18.867</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:fmla="=OR(AND(1*RIGHT(LEFT(C4,10),4)=B4,1*RIGHT(LEFT(C4,5),2)>6),AND(1*RIGHT(LEFT(C4,10),4)=B4+1,1*RIGHT(LEFT(C4,5),2)<7))" x:err="#VALUE!">#VALUE!</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C3,10),4)=B3,1*RIGHT(LEFT(C3,5),2)>6),AND(1*RIGHT(LEFT(C3,10),4)=B3+1,1*RIGHT(LEFT(C3,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C3,10),4)=B3,1*RIGHT(LEFT(C3,5),2)>6),AND(1*RIGHT(LEFT(C3,10),4)=B3+1,1*RIGHT(LEFT(C3,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>4</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1/09/2010 13:47:18.93</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:fmla="=OR(AND(1*RIGHT(LEFT(C5,10),4)=B5,1*RIGHT(LEFT(C5,5),2)>6),AND(1*RIGHT(LEFT(C5,10),4)=B5+1,1*RIGHT(LEFT(C5,5),2)<7))" x:err="#VALUE!">#VALUE!</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C4,10),4)=B4,1*RIGHT(LEFT(C4,5),2)>6),AND(1*RIGHT(LEFT(C4,10),4)=B4+1,1*RIGHT(LEFT(C4,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C4,10),4)=B4,1*RIGHT(LEFT(C4,5),2)>6),AND(1*RIGHT(LEFT(C4,10),4)=B4+1,1*RIGHT(LEFT(C4,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>5</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10/12/2010 16:30:29.987</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:fmla="=OR(AND(1*RIGHT(LEFT(C6,10),4)=B6,1*RIGHT(LEFT(C6,5),2)>6),AND(1*RIGHT(LEFT(C6,10),4)=B6+1,1*RIGHT(LEFT(C6,5),2)<7))" x:bool="FALSE">TRUE</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C5,10),4)=B5,1*RIGHT(LEFT(C5,5),2)>6),AND(1*RIGHT(LEFT(C5,10),4)=B5+1,1*RIGHT(LEFT(C5,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C5,10),4)=B5,1*RIGHT(LEFT(C5,5),2)>6),AND(1*RIGHT(LEFT(C5,10),4)=B5+1,1*RIGHT(LEFT(C5,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>6</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10/09/2009 13:47:19.507</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:fmla="=OR(AND(1*RIGHT(LEFT(C7,10),4)=B7,1*RIGHT(LEFT(C7,5),2)>6),AND(1*RIGHT(LEFT(C7,10),4)=B7+1,1*RIGHT(LEFT(C7,5),2)<7))" x:err="#VALUE!">FALSE</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C6,10),4)=B6,1*RIGHT(LEFT(C6,5),2)>6),AND(1*RIGHT(LEFT(C6,10),4)=B6+1,1*RIGHT(LEFT(C6,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C6,10),4)=B6,1*RIGHT(LEFT(C6,5),2)>6),AND(1*RIGHT(LEFT(C6,10),4)=B6+1,1*RIGHT(LEFT(C6,5),2)<7))</TD></TR></TBODY></TABLE>

The formula column works only if the date is a double digit.

Let me know if there is an easier way of doing this.

Thanks in advance.

 

[Haseeb Avarakkan]

Hello, try,

C2, copy down...

=YEAR(LOOKUP(1E+307,--LEFT(B2,{9,10})))=$A$2

 

[108monkeys]

Thank you!

 

[108monkeys]

<TABLE style="WIDTH: 855pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1139 border=0 x:str><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 599pt; mso-width-source: userset; mso-width-alt: 29184" width=798><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 23pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=30 height=17></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=63>A</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 115pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=153>B</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=95>C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; WIDTH: 599pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=798>D</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">YEAR</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">DATE AS TEXT</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">RETURNS</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver">FORMULA USES THE YEAR AND THE DATE WHICH IS STORED AS TEXT => SHOULD RETURN TRUE OR FALSE</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>2</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2010</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1/09/2010 13:47:18.867</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:err="#VALUE!" x:fmla="=OR(AND(1*RIGHT(LEFT(C3,10),4)=B3,1*RIGHT(LEFT(C3,5),2)>6),AND(1*RIGHT(LEFT(C3,10),4)=B3+1,1*RIGHT(LEFT(C3,5),2)<7))">#VALUE! </TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C2,10),4)=B2,1*RIGHT(LEFT(C2,5),2)>6),AND(1*RIGHT(LEFT(C2,10),4)=B2+1,1*RIGHT(LEFT(C2,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C2,10),4)=B2,1*RIGHT(LEFT(C2,5),2)>6),AND(1*RIGHT(LEFT(C2,10),4)=B2+1,1*RIGHT(LEFT(C2,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>3</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1/09/2010 13:47:18.867</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:err="#VALUE!" x:fmla="=OR(AND(1*RIGHT(LEFT(C4,10),4)=B4,1*RIGHT(LEFT(C4,5),2)>6),AND(1*RIGHT(LEFT(C4,10),4)=B4+1,1*RIGHT(LEFT(C4,5),2)<7))">#VALUE!</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C3,10),4)=B3,1*RIGHT(LEFT(C3,5),2)>6),AND(1*RIGHT(LEFT(C3,10),4)=B3+1,1*RIGHT(LEFT(C3,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C3,10),4)=B3,1*RIGHT(LEFT(C3,5),2)>6),AND(1*RIGHT(LEFT(C3,10),4)=B3+1,1*RIGHT(LEFT(C3,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>4</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1/03/2011 13:47:18.93</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:err="#VALUE!" x:fmla="=OR(AND(1*RIGHT(LEFT(C5,10),4)=B5,1*RIGHT(LEFT(C5,5),2)>6),AND(1*RIGHT(LEFT(C5,10),4)=B5+1,1*RIGHT(LEFT(C5,5),2)<7))">#VALUE!</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C4,10),4)=B4,1*RIGHT(LEFT(C4,5),2)>6),AND(1*RIGHT(LEFT(C4,10),4)=B4+1,1*RIGHT(LEFT(C4,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C4,10),4)=B4,1*RIGHT(LEFT(C4,5),2)>6),AND(1*RIGHT(LEFT(C4,10),4)=B4+1,1*RIGHT(LEFT(C4,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>5</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10/12/2010 16:30:29.987</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:fmla="=OR(AND(1*RIGHT(LEFT(C6,10),4)=B6,1*RIGHT(LEFT(C6,5),2)>6),AND(1*RIGHT(LEFT(C6,10),4)=B6+1,1*RIGHT(LEFT(C6,5),2)<7))" x:bool="FALSE">TRUE</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C5,10),4)=B5,1*RIGHT(LEFT(C5,5),2)>6),AND(1*RIGHT(LEFT(C5,10),4)=B5+1,1*RIGHT(LEFT(C5,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C5,10),4)=B5,1*RIGHT(LEFT(C5,5),2)>6),AND(1*RIGHT(LEFT(C5,10),4)=B5+1,1*RIGHT(LEFT(C5,5),2)<7))</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" align=right height=17 x:num>6</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">10/09/2009 13:47:19.507</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=middle x:err="#VALUE!" x:fmla="=OR(AND(1*RIGHT(LEFT(C7,10),4)=B7,1*RIGHT(LEFT(C7,5),2)>6),AND(1*RIGHT(LEFT(C7,10),4)=B7+1,1*RIGHT(LEFT(C7,5),2)<7))">FALSE</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT-COLOR: windowtext; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=OR(AND(1*RIGHT(LEFT(C6,10),4)=B6,1*RIGHT(LEFT(C6,5),2)>6),AND(1*RIGHT(LEFT(C6,10),4)=B6+1,1*RIGHT(LEFT(C6,5),2)<7))">=OR(AND(1*RIGHT(LEFT(C6,10),4)=B6,1*RIGHT(LEFT(C6,5),2)>6),AND(1*RIGHT(LEFT(C6,10),4)=B6+1,1*RIGHT(LEFT(C6,5),2)<7))</TD></TR></TBODY></TABLE>
Oops! The formulae did not work for what I require.

I need to identify the financial year. So Column C should return:

TRUE
TRUE
TRUE
TRUE
FALSE

This formulae will work for 2010.

What formulae is required to lookup the financial year from 1/07/2010 to 31/06/2011?

 

[Haseeb Avarakkan]

Try,

C2, copy down...

=AND(B2+0>=DATE($A$2,7,1),B2+0<=DATE($A$2+1,6,30))

 

[108monkeys]

You are a Star!

 

[Peter_SSs]

Would this also work for you?

=YEAR(EDATE(B2,-6))=$A$2

 

[108monkeys]

Hi Peter,

I tried =YEAR(EDATE(B2,-6))=$A$2 without success. It returns: #NAME?

 

[Peter_SSs]

Hi Peter,

I tried =YEAR(EDATE(B2,-6))=$A$2 without success. It returns: #NAME?

That would indicate you don't have Analysis ToolPak installed/activated. If Excel 2003, look in Tools|Add-Ins...|check Analysis ToolPak (if it appears in the list)|OK

 

[108monkeys]

Very Nice! Thanks.