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

108monkeys

New Member
Joined
Jan 13, 2008
Messages
23
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hello, try,

C2, copy down...

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

108monkeys

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

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try,

C2, copy down...

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Would this also work for you?

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

108monkeys

New Member
Joined
Jan 13, 2008
Messages
23
Office Version
  1. 2010
Hi Peter,

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,119
Messages
5,835,500
Members
430,362
Latest member
ConnerJ

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
Top