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>

 

[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>

sorry i missing it...

should be :

criteria:
- if data ">=July 2010" ----show 2011
- if data between 2010-01-xxx to 2010-06-xxxxx -----show 2010

 

[Rick Rothstein]

sorry i missing it...

should be :

criteria:
- if data ">=July 2010" ----show 2011
- if data between 2010-01-xxx to 2010-06-xxxxx -----show 2010

Then why are the answers for 1997-12-269, 2000-12-1153 and 2015-09-0000215 the years 1997, 2000 and 2015 instead of 1998, 2001 and 2016?

 

[muhammad susanto]

Why is the answer for 2015-09-0000215 the year 2015 instead of 2016?

hi Ricks, for 2015-09-0000215 still 2015

my format data is general

 

[JLGWhiz]

=IF(AND(MID(A2,6,2)>="07",MID(A2,6,2)<="12"),LEFT(A2,4)+1,LEFT(A2,4))

You can put this in cell A2 then drag it down and it should fill in the correct numbers for you.

 

[Rick Rothstein]

hi Ricks, for 2015-09-0000215 still 2015

Why? The month 09 (September) is greater than July and you said "if data '>=July 2010' ----show 2011".

Also look at my last message again because I edited it to question two more of your examples?

 

[JLGWhiz]

=IF(AND(MID(A2,6,2)>="07",MID(A2,6,2)<="12"),LEFT(A2,4)+1,LEFT(A2,4))

You can put this in cell A2 then drag it down and it should fill in the correct numbers for you.

This shoud be put in cell B2 and drag down, not A2.

 

[muhammad susanto]

=IF(AND(MID(A2,6,2)>="07",MID(A2,6,2)<="12"),LEFT(A2,4)+1,LEFT(A2,4))

You can put this in cell A2 then drag it down and it should fill in the correct numbers for you.

hi thanks but not fully work for :

2011-08-2154 --- 2012 ----2011 (should be)
2012-07-4154-----2013--- 2012 (should be)

note :
- if data ">=July 2010" ----show 2011
- if data between 2010-01-xxx to 2010-06-xxxxx -----show 2010

 

[Rick Rothstein]

hi thanks but not fully work for :

2011-08-2154 --- 2012 ----2011 (should be)
2012-07-4154-----2013--- 2012 (should be)

Why? Why?? Why???

In light of this statement by you...

note :
- if data ">=July 2010" ----show 2011
- if data between 2010-01-xxx to 2010-06-xxxxx -----show 2010

it seems like the year should increase by 1. You have to explain the logic to us why that is not the case.

 

[muhammad susanto]

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