hi..
i have data contains date and text format, i want take year with criteria :
if date between ">=July 2010 to Dec 2010" take 2011 otherwise like this below :
30/12/1986----1986
30/7/2012-----2012
1996-08-0125--1996
2015-12-4526--2015
sorry, i want to single formula not helper column
please, check my attachment..
john m
Hi. I am a bit confused by the dates in the first few rows. What is the 105th July 2010???
=IF(ISNUMBER(A2),IF(AND(A2>=DATE(2010,7,1),A2<=DATE(2010,12,31)),2011,YEAR(A2)),LEFT(A2,4))
does almost everything you wanted, except for the 105th July!!! Can you explain what that cell (A4) is meant to represent???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
d6=if(And(A6>$F$1,A6<$F$2),2011,year(A6))
in which f1 = 01-07-2010
in which f2 = 31-12-2010
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
OK. I decided to guess what you wanted... and came up with this, which seems to do what you asked for!!
=IF(ISNUMBER(A4),IF(AND(A4>=DATE(2010,7,1),A4<=DATE(2010,12,31)),2011,YEAR(A4)),IF(AND(--LEFT(A4,4)=2010,--MID(A4,6,2)>=7),2011,LEFT(A4,4)))
Last edited by Glenn Kennedy; 09-13-2015 at 10:24 AM.
hi Glenn, working like a charm....Originally Posted by Glenn Kennedy
thanks a lot!!!!!!
Or this
=IFERROR(IF(AND(MONTH(A2)>=7,YEAR(A2)=2010),2011,YEAR(A2)),--LEFT(A2,4))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
This looks a crosspost to me.
If so, please read the forumrules about crossposting.
http://www.mrexcel.com/forum/excel-q...s-formula.html
USE THIS FORMULA IN C2 (see attachment)
PHP Code:=CHOOSE(ISNUMBER(A2)+1,IF(ISTEXT(A2),IF(MONTH((MID(A2,1,LEN(A2)-1))*1)<7,YEAR((MID(A2,1,LEN(A2)-1))*1),YEAR((MID(A2,1,LEN(A2)-1))*1)+1),""),IF(ISNUMBER(A2),IF(AND(MONTH(A2)>=7,MONTH(A2)<=12,YEAR(A2)=2010),2011,YEAR(A2)),""))
See the 2nd sheet of this file
EXTRACT DATE Salim 1.xlsx
an improved one
=IF(AND(--TEXT(LEFT(A2,10),"YYYY")=2010,--TEXT(LEFT(A2,10),"m")>=7),2011,--TEXT(LEFT(A2,10),"YYYY"))
hi guys...i found new problem for
1989-1-1054 ----#value---should be 1989
2006-6-15 -----#value---should be 2006
etc..
if number's month = 2 character the formula is working well otherwise not working
1989-01-1054----1989
2006-06-15------2006
how do fixed?
or... just by extending the formula that I used the first time like this:
Formula:
=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(SUBSTITUTE("-"&$A2,"-",REPT(" ",125)),125*COLUMNS($A:B),125)>=7),2011,LEFT(A2,4)))
will do it.
It would also be possible to adapt Alkey's tidier solution, but it would depend on what digits can appear in the last group in A 1 to A3. Is 405 or 656 (ie anything where the first two digits exceed the number of days in a month) possible?
Last edited by Glenn Kennedy; 09-14-2015 at 02:41 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks