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

108monkeys

New Member
Joined
Jan 13, 2008
Messages
25
Office Version
  1. 2010
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
<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?
 
Upvote 0
Would this also work for you?

=YEAR(EDATE(B2,-6))=$A$2
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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