hi all....
i want to extract the number became year:
1995-7-xxxxxx ======= 1995
2015-12-xxx========= 2015
2010-07-xxxx======== 2011
2010-06-xxxx======== 2010
criteria:
- if data ">=July 2010" ----show 2011
- if data between 2010-01-xxx to 2010-06-xxxxx -----show 2010
any assistance, much appreciated..
john m
Hi,
If your data starts in A1 then put this formula in another cell and drag down:
Formula:
Please Login or Register to view this content.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
hi abousetta, not fully working
e.g
2010-06-2154 ---2010 (your formula correct)
2010-07-2154--2011 (your formula correct)
2011-06-2154 --2011 (your formula correct)
2011-07-2154 --2012 (your formula false), should be keep 2011 not 2012
note : only in 2010 have criteria like in post #1
=IF(VALUE(SUBSTITUTE(RIGHT(LEFT(A1,FIND("-",A1)+2),2),"-",""))>6,VALUE(LEFT(A1,4))+1,VALUE(LEFT(A1,4)))
this one will do your job.
thanks but not fully workingOriginally Posted by nahidking
=IF(VALUE(SUBSTITUTE(RIGHT(LEFT(A1,FIND("-",A1)+2),2),"-",""))>6,VALUE(LEFT(A1,4))+1,VALUE(LEFT(A1,4)))
this one will do your job.
only in year 2010 have criteria
2011-05-2154 ---2011 (correct)
2011-07-2154 ---2012 (incorrect) ---2011 (should be)
=left(a1,4)+(--left(a1,4)=2010)*(--substitute(mid(a1,5,3),"-","")>6)
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
hi Siva, 100% working!!!!
Data Range
Data Range
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks