Solved

I need to update the following formula

Posted on 2014-03-11
17
273 Views
Last Modified: 2014-03-12
I need to update the following formula:

=IF(AND(g10>J10,K10>g10),"test1",IF(g10>K10,"test2",""))


I neeed to update it to include: (LEFT(G10, LEN(G10)-4). I cant get the sytnax right and it keeps erroring out.

G10 has a space and (X) in the cell that I need to be able to remove and look just at the date.
0
Comment
Question by:wrt1mea
  • 7
  • 5
  • 3
  • +2
17 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39920742
will you post your sample WB?
0
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39920758
pls share some example data/sheet
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39920765
Not sure what you are looking for without the sample but it sounds like you maybe only want to read the first 4 characters of G10 or you want everthing after the first 4 characters?

left(g10,4) will get the first 4

RIGHT(g10, LEN(g10)-4) will get everything after the first 4
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 1

Author Comment

by:wrt1mea
ID: 39920849
Here is a sample
Ex-Ech-3-11-14.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39920873
@pony10us
=(LEFT(G10, LEN(G10)-4)

Will give everything but the last 4 characters.

Thanks
Rob H
0
 
LVL 1

Author Comment

by:wrt1mea
ID: 39920879
Thats all I want, everything except the last 4 in G10....


Now if someone could put that in the formula, that would be great. I kept getting errors.
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39920897
Try this:

=IF(AND(LEFT(G10,LEN(G10)-4)*1>J10,K10>LEFT(G10,LEN(G10)-4)*1),"test1",IF(LEFT(G10,LEN(G10)-4)*1>K10,"test2",""))

I have included *1 to force excel to recognise it as a number; maybe thats why it was still giving errors.

Thanks
Rob H
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39920900
To get syntax right I typed the formula in a separate cell:

=LEFT(G10,LEN(G10)-4)*1

Then copied the text of the formula to the clipboard, press F2 and highlight the formula in the formula bar and Ctrl C.

Then selected T10 and U10 and did edit replace:

Find         G10
Replace   LEFT(G10,LEN(G10)-4)*1  (pasted in)

Click Replace All.

Thanks
Rob H
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39920936
Rob,

Wouldn't this work as well?

=IF(AND(LEFT(G10,LEN(G10)-4)>J10,(K10>G10)),"test1",IF(LEFT(G10,LEN(G10)-4)>K10,"test2",""))

At least it seems to work in my tests.

EDIT:

Actually,  I just tested it agains some lines that I think should have resulted in "test1" (line 14 for example) and it didn't so I guess my formula isn't quite correct.
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 39920962
GREAT JOB!

THANKS TO EVERYONE FOR THE HELP!
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39921373
Pony - what locale are you using?

The sample data that came through was in mm/dd/yyyy format so I had to tweak it a little bit first.

The sample on row 10 had 6/30/2014 (E) so I manually changed to 30/6/2014 (E) to allow for UK date recognition.

Thanks
Rob
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39921585
Rob,

I am in US so no need to alter the format from the original.

I wanted to play around some more to try and see where it was failing.

Givens:

G10 = 6/30/14 (E)
H10 = 9/24/14
J10 = 4/12/14
K10 = 11/12/14


In Cell L10 I put:    =(LEFT(G10,LEN(G10)-4))   which returned     6/30/14

In Cell M10 I put:    =IF(L10>J10,"yes","no")     which returned       yes
(6/30/14>4/12/14)

In Cell N10 I put:    =IF(K10>L10,"yes","no")     which returned       no
(this returned incorrectly)  

I changed Cell N10 to:    =IF(K10>L10,K10,L10)     which returned  6/30/14
(also incorrect)

The format of both K10 and L10 is date 3/14/14 so not sure why everything worked until the final test.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39921903
Although the LEFT function returned what looks like a date, is it actually a date. Format it as comma or another number format and it should be 41820; if not then its not a true date.

Alternatively shrink the column so the value won't fit. A number will go to ### whereas text will either partly disappear or run onto the next cell.

I have replicated the little test above and the text value "6/30/14" returns Yes and No as per your results but replacing the 6/30/14 with (for my locale) 30/06/14 returns the correct answers.

Alternative to the *1 would be DATEVALUE(LEFT(G10,LEN(G10)-4)) which should give a date in your locale as it will recognise the format of 6/30/14 as being 30 June, that didn't work for UK locale.

Thanks
Rob
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39922030
Rob,

Thank you,  after playing around I found that the formula in L10

=(LEFT(G10,LEN(G10)-4))

does NOT return a date.  Not sure what it returns but not the date 6/3/14 even though the cell is formated for that date format.  I formated the cell as General and it still looked the same.  

I changed it to:

=(LEFT(G10,LEN(G10)-4))*1

and it returned the date value (when changing the cell format to general it gave the number 41820

All other calculations work properly with the *1 in the proper places.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39922122
The LEFT part of the formula purely pulls a string of text. Putting the *1 on thend tells Excel to try and do some maths with it. If it were ordinary text it would fail but its clever enough to recognise it as a date so does the necessary calcs and returns the date.

Thanks
Rob
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39923602
Thank you again Rob.  That's why I like EE.  Always something new to learn while helping others at the same time.  :)
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39923644
Every day is a school day!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question