Extract Some Number To Be Year

[muhammad susanto]

hi all...

how to extract number this below :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]data[/TD]
[TD]expected result (year)[/TD]
[/TR]
[TR]
[TD]1997-12-269[/TD]
[TD]1997[/TD]
[/TR]
[TR]
[TD]2000-12-1153[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]2006-2-349[/TD]
[TD]2006[/TD]
[/TR]
[TR]
[TD]2015-09-0000215[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]2010-05-009[/TD]
[TD]2010[/TD]
[/TR]
[TR]
[TD]2010-06-00002654[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]2010-07-000023[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]2011-01-326[/TD]
[TD]2011[/TD]
[/TR]
</tbody>[/TABLE]

criteria:
- if data ">=July 2010" ----show 2011
- if data "<july 2010"="" ----show="" <font="" color="#ff0000">2010

note just clue :
e.g. 2010-05-009 ===2010 as year, 05 as month....


thank for your helps..

m.susanto


</july>

 

[azumi]

Assume your date start on A1

put on B2 and copied down:

=IF(AND(DATE(2010,7,1)>=DATE(LEFT(A1,4)+0,TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),100,99))+0,1),DATE(2010,7,1)<=DATE(LEFT(A1,4)+0,TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),100,99))+0,1)),LEFT(A1,4)+0+1,LEFT(A1,4)+0)

 

[Rick Rothstein]

hi Ricks...
i want to explain any more but my language not enough well, i just to illustrated with direct my case but the core :
only in 2010 increase by 1 with criteria between >=july 2010 to Dec 2010 the result is 2011 the for otherwise year (not 2010) must take 4 digits left number, like eg.

1995-05-xxxxx -----1995
2001-12-xxxxx------2001
2015-11-xxxxx------2015
2010-06-xxxxx------2010
2010-07-xxxxx------2011
2011-01-xxxxx------2011

I am not sure I understand the logic that restricts adding 1 for July thru December of 2010 only, but it that is what you want, give this formula a try...

=LEFT(A2,4)+AND(LEFT(A2,4)="2010",0+MID(A2,6,1+(MID(A2,7,1)<>"-"))>6)

 

[bosco_yip]

Or try this,

=LEFT(A1,4)+(ABS((LEFT(A1,8)&1)-40422)<92)

 

[muhammad susanto]

I am not sure I understand the logic that restricts adding 1 for July thru December of 2010 only, but it that is what you want, give this formula a try...

=LEFT(A2,4)+AND(LEFT(A2,4)="2010",0+MID(A2,6,1+(MID(A2,7,1)<>"-"))>6)

hi Ricks, great work, that's i mean!!! thank u

 

[Rick Rothstein]

Or try this,

=LEFT(A1,4)+(ABS((LEFT(A1,8)&1)-40422)<92)

Fails for the third one down on Message #1, namely this one... 2006-2-349 (note the single digit month number).

 

[bosco_yip]

Fails for the third one down on Message #1, namely this one... 2006-2-349 (note the single digit month number).

Thanks Rick,

then, try this modified one :

=LEFT(A1,4)+(ABS((LEFT(A1,FIND("-",A1,6))&1)-40422)<92)