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

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,105
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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 :
Code:
[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
Code:
=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
 
hi guys..it works for me

Code:
=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
 
Last edited:
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Don't know why the above posted formula show incompletely ?
 
Last edited:
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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.?
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
hi bosco...

Code:
[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...
 
Upvote 0

Forum statistics

Threads
1,226,737
Messages
6,192,737
Members
453,753
Latest member
SORD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top