Combine 2 formula into/be i formula - rick's formula

[muhammad susanto]

hi all....

i want to combine 2 formula into 1 formula (Rick's formula)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]data (col.a)[/TD]
[TD]result after use formula A[/TD]
[TD]result after use formula B[/TD]
[TD]single formula (combine) ??[/TD]
[/TR]
[TR]
[TD]08/12/2005 (as date)[/TD]
[TD]2005-12-08[/TD]
[TD]2005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/07/1992 (as date)[/TD]
[TD]1992-07-01[/TD]
[TD]1992[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1986-7-45 (as text)[/TD]
[TD]1986-7-45[/TD]
[TD]1986[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2001-10-380 (as text)[/TD]
[TD]2001-10-380[/TD]
[TD]2001[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

formula A :

[COLOR=#333333]=IF(COUNTIF(A1,"*-*"),A1,TEXT(A1,"yyyy-mm-dd"))[/COLOR]

http://www.mrexcel.com/forum/excel-...-number-different-format-date-vs-general.html

then
formula B

=LEFT(B1,4)+(--LEFT(B1,4)=2010)*(--SUBSTITUTE(MID(B1,5,3),"-","")>6)

http://www.mrexcel.com/forum/excel-questions/880218-extract-some-number-year-2.html

how do make it. It's possible?

thank in advance..

m.susanto

 

[gaz_chops]

Try

=LEFT(IF(COUNTIF(A1,"*-*"),A1,TEXT(A1,"yyyy-mm-dd")),4)+(--LEFT(IF(COUNTIF(A1,"*-*"),A1,TEXT(A1,"yyyy-mm-dd")),4)=2010)*(--SUBSTITUTE(MID(IF(COUNTIF(A1,"*-*"),A1,TEXT(A1,"yyyy-mm-dd")),5,3),"-","")>6)

 

[Rick Rothstein]

hi all....

i want to combine 2 formula into 1 formula (Rick's formula)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]data (col.a)[/TD]
[TD]result after use formula A[/TD]
[TD]result after use formula B[/TD]
[TD]single formula (combine) ??[/TD]
[/TR]
[TR]
[TD]08/12/2005 (as date)[/TD]
[TD]2005-12-08[/TD]
[TD]2005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/07/1992 (as date)[/TD]
[TD]1992-07-01[/TD]
[TD]1992[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1986-7-45 (as text)[/TD]
[TD]1986-7-45[/TD]
[TD]1986[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2001-10-380 (as text)[/TD]
[TD]2001-10-380[/TD]
[TD]2001[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

It is not clear to me what you want the combined formula to output... can you fill in the "single formula (combine)" column so we can see what you want?

 

[István Hirsch]

If you want to get the year in one step, try this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",20)),20)),4)

 

[gaz_chops]

If you want to get the year in one step, try this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",20)),20)),4)

Doesn't appear to work on actual dates, only text.

 

[Rick Rothstein]

If you want to get the year in one step, try this:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",20)),20)),4)

Or maybe this...

=0+LEFT(TEXT(A1,"yyyy"),4)

 

[István Hirsch]

Doesn't appear to work on actual dates, only text.

Thanks, you may be right, I have checked it only in my system where „/” is not a date separator. That can be the cause of the difference.

 

[muhammad susanto]

hi guys...thanks but it's not i want it..

I WANT TO EXTRACT YEAR BUT FOR "DATE >=JULY 2010" TO DEC 2010 put year "2010" OTHERWISE NORMALLY

please, check my attachment

https://app.box.com/s/bzwqwh1tftphn360du4xh4vep8smqxar

 

[muhammad susanto]

hi guys..it works for me

=IF(ISNUMBER(A2);IF(AND(A2>=DATE(2010;7;1);A2<=DATE(2010;12;31));2011;YEAR(A2));IF(AND(--LEFT(A2;4)=2010;--MID(A2;6;2)>=7);2011;LEFT(A2;4)))

any more shorter? or other idea?

 

[gaz_chops]

Using Rick's solution, try

=IF(AND(ISNUMBER(A2),A2>=DATE(2010,7,1),A2<=DATE(2010,12,31)),2011,0+LEFT(TEXT(A2,"yyyy"),4))