LEFT Function to read the first date in a cell
Hi,
Often use this forum for help with my formulas but this is my first post as a member.
Version: Excel 2007
OS: Windows XP
So here's my query...
Cell A1 containts: "01/01/1990 to 01/01/2005"
I want to read the first date in the cell and return "Yes" if the date is before 01/01/2004
Current formula: "=IF(LEFT(A1,10)="<DATE(01,01,2004)","Yes","No")"
The formula keeps returning "No" regardless of the dates in the cell.
Hope this is clear and I havn't forgotten any info!
Thanks,
Oscar
May be:
=IF(--LEFT(A1,10)<DATEVALUE("01/01/2014"),"Yes","No")
Quang PT
=IF(LEFT(A1,10)+0<DATE(2004,1,1),"Yes","No")
note date()function uses format year,month,day
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Or if your dates are inconsistent
=IF(LEFT(A1,SEARCH(" ",A1)-1)+0<=DATE(2004,1,1),"YES","NO")
Note; your "DATE" Function was set up incorrectly. It's year, month, day.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thank you to all for your quick responses and great advice!
Although all of your suggestions fixed my problem, I'm still having issues with the overall formula.
I feel like my conditions require IF,AND,OR,LEFT formulas all together, and I can't seem to fit them all in.
Basically I need to return 8 different results depending on a set of data.
I can get it almost perfect, but was having trouble with that date issue, and also the OR function which needs to fit right in the middle.
I've attached a simplified version so you can clearly see what I'm trying to do. Would really appreciate some help with this. My formula satisfies all conditions, except when it gets to whether there's a value in box1 OR box2. (Will make sense with the attachment)
The problem is creating 1 formula for everything.
Thanks again all,
Oscar
Could you pls clarify this:
H2 said : "...has value in either box1 or box2" means box1 (or box 2)>0 ?
H5 said: "...has no value in either box1 or box2" means box1(or box2)=0?
I saw that the sample is different :both boxes have value or no value.
H2: Box1 (or box2)>0
H5: Box1 (and box2)=0
In the real work sheet sometimes box1 has a value and box2 doesn't have a value, and vice versa.
Another question: is there negative in boxes?
Last edited by bebo021999; 04-28-2014 at 12:41 PM. Reason: typo
There is never a negative in the boxes, it is always a value higher than 0, so >0 is ok
How about in F2:
Drag down
Please Login or Register to view this content.
Place this in F2 and drag down:
Formula:
Please Login or Register to view this content.
Last edited by playaller; 04-29-2014 at 06:56 PM.
Shelton A.
If Helpful, Add Reputaion!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks