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

 

[bosco_yip]

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?

Try,

=IF(N(A2),(A2>DATE(2010,6,30))*(A2<DATE(2011,1,1))+YEAR(A2),(--LEFT(A2,4)=2010)*(--MID(A2,6,2)>6)+LEFT(A2,4))

Regards

 

[bosco_yip]

Don't know why the above posted formula show incompletely ?

 

[Rick Rothstein]

Don't know why the above posted formula show incompletely ?

Whenever you have a less than sign (<) followed by a letter, this forum's comment processor thinks it is the start of an HTML tag and waits for the greater than sign (>) to close it off. The way around this problem is to put a space between the less than sign and the letter. Here is your formula in its entirety...

=IF(N(A2),(A2>DATE(2010,6,30))*(A2< DATE(2011,1,1))+YEAR(A2),(--LEFT(A2,4)=2010)*(--MID(A2,6,2)>6)+LEFT(A2,4))

 

[bosco_yip]

Whenever you have a less than sign (<) followed by a letter, this forum's comment processor thinks it is the start of an HTML tag and waits for the greater than sign (>) to close it off. The way around this problem is to put a space between the less than sign and the letter. Here is your formula in its entirety...

=IF(N(A2),(A2>DATE(2010,6,30))*(A2< DATE(2011,1,1))+YEAR(A2),(--LEFT(A2,4)=2010)*(--MID(A2,6,2)>6)+LEFT(A2,4))

Thanks Rick.

 

[bosco_yip]

Another shorter one :

=LEFT(TEXT(A2,"yyy"),4)+(ABS(IF(N(A2),A2,LEFT(A2,8)&1)-40451.5)<92)

Regards

 

[muhammad susanto]

Whenever you have a less than sign (<) followed by a letter, this forum's comment processor thinks it is the start of an HTML tag and waits for the greater than sign (>) to close it off. The way around this problem is to put a space between the less than sign and the letter. Here is your formula in its entirety...

=IF(N(A2),(A2>DATE(2010,6,30))*(A2< DATE(2011,1,1))+YEAR(A2),(--LEFT(A2,4)=2010)*(--MID(A2,6,2)>6)+LEFT(A2,4))

hi..i have a problem the formula not working well for this :

1996-8-643 (text format) ---#VALUE!----should be 1996
1996-1-64 -----#VALUE!----should be 1996
1997-4-884004 --#VALUE!---should be 1997

note :
if count number "month" is 2 character the formula working well, otherwise
1996-08-643 ----1996 (result)
1996-8-643 -----#VALUE!
etc..

could you help me out. guys.?

 

[bosco_yip]

hi..i have a problem the formula not working well for this :

1996-8-643 (text format) ---#VALUE!----should be 1996
1996-1-64 -----#VALUE!----should be 1996
1997-4-884004 --#VALUE!---should be 1997

note :
if count number "month" is 2 character the formula working well, otherwise
1996-08-643 ----1996 (result)
1996-8-643 -----#VALUE!
etc..

could you help me out. guys.?

Try this modified one :

=IF(N(A2),(A2>DATE(2010,6,30))*(A2< DATE(2011,1,1))+YEAR(A2),(--LEFT(A2,4)=2010)*(--MID(A2,6,FIND("-",A2,6)-6)>6)+LEFT(A2,4))

or, this shorter modified version :

=LEFT(TEXT(A2,"yyy"),4)+(ABS(IF(N(A2),A2,LEFT(A2,FIND("-",A2,6))&1)-40451.5)<92)

Regards

 

[muhammad susanto]

hi bosco...

[COLOR=#333333]=LEFT(TEXT(A2,"yyy"),4)+(ABS(IF(N(A2),A2,LEFT(A2,FIND("-",A2,6))&1)-40451.5)<92)[/COLOR]

you are really VIP!!!! WORK WELL 100%...

THANKS FOR YOUR KINDNESS...