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)

data (col.a)	result after use formula A	result after use formula B	single formula (combine) ??
08/12/2005 (as date)	2005-12-08	2005
01/07/1992 (as date)	1992-07-01	1992
1986-7-45 (as text)	1986-7-45	1986
2001-10-380 (as text)	2001-10-380	2001

<tbody>
</tbody>

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)

data (col.a)	result after use formula A	result after use formula B	single formula (combine) ??
08/12/2005 (as date)	2005-12-08	2005
01/07/1992 (as date)	1992-07-01	1992
1986-7-45 (as text)	1986-7-45	1986
2001-10-380 (as text)	2001-10-380	2001

<tbody>
</tbody>

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