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

108monkeys

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

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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
21
<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
46,886
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Would this also work for you?

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,886
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,030
Members
410,721
Latest member
adi772
Top