Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Condions in Excel

Posted on 2011-05-05
Medium Priority
282 Views
I have a spreadsheet that I need to do some review on on an ongoing basis. Is there any way to do complex conditional formatting ...

if k1 = k2 and timediff of (c1 & h1) - (c2&h2) <=60 and g1 = 47 and g2 = 2   then z2=TRUE

NEXT LINE
if k2 = k3 and timediff of (c2 & h2) - (c3&h3) <=60 and g2 = 47 and g3 = 2   then z3=TRUE

And so on through the spreadsheet
5-5-2011-5-15-18-PM.jpg
0
Question by:hwassinger
• 3

LVL 33

Expert Comment

ID: 35702079
Like this?

=IF(AND(K1=K2;(C1+H1)-(C2-H2)<=60;G1=47;G2=2);"True";"False")

jppinto
0

LVL 33

Accepted Solution

jppinto earned 2000 total points
ID: 35702169
=IF(AND(K1=K2,(DATE(LEFT(C1,4);MID(C1,5,2),RIGHT(C1,2))+H1)-(DATE(LEFT(C2,4),MID(C2,5,2);RIGHT(C2,2))+H2)<=60,G1=47,G2=2),"True","False")

It would help if you posted a sample sheet for testing purpose...
0

LVL 33

Expert Comment

ID: 35702210

jppinto
Formula.xls
0

Author Comment

ID: 35703380
When I poaste the formula into a cell it only displays the formula, not the TRUE as your exanple did, what am I missing?
0

LVL 17

Expert Comment

ID: 35706047
If it is only displaying the formula in the cell then you have probably missed the equals off the front of it (=IF(An......)
0

## Featured Post

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.