[Solved/Closed]
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
-
sns -
Hello,

Ok here is the situation

Data is

9:00 am
4:30 pm

Now i want the If Formula to function , that if the range between 9:00 am to 4:30 pm happens the value turns to
true , but if the range exceed like 4:31 pm , i want the false value to reflect

in other words

Example

A1 = 9:00 AM
A2 = 4:30 PM

any time like before 9:00 am and after 4:30 pm should be false and data between 9-4:30 should be true.

Waiting for expert opinion.

How can I have my entire row highlighted using the conditional format
function based on the value of only one cell? I can only get the single cell
to highlight.

My data is simple:
PO# Inv# Amt Status
ABC 123 50.25 Partial

When the status reads "Partial" I want the entire row to be highlighted, not
just the cell that reads "Partial"

Please Do example the calclation in steps for easy understanding

## 12 replies

Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
Hi,

A1 = 9:00 AM
A2 = 4:30 PM

Formula=IF(AND(A1>=0.375,A2<=0.1875),"TRUE","FALSE")

First of all write in B1 = value(A1) = 0.375
THEN B2= VALUE(A2) = 0.1875

Theses are the values of 09:00 AM & 04:30 am. Now you can use them in Formula.

How can I have my entire row highlighted using the conditional format
function based on the value of only one cell? I can only get the single cell
to highlight.

My data is simple:
PO# Inv# Amt Status
ABC 123 50.25 Partial

When the status reads "Partial" I want the entire row to be highlighted, not
just the cell that reads "Partial"

Suppose you have data from A2 to D20 .....

1. Now first select cell A2 and select the whole data upto D20 by dragging mouse ( hope you can do it easily) but make sure your first cell is A2 ..
2. Now go to format > conditional formatting > Condition 1 -- Formula is and now write this =\$D2="Partial"
3. select any color and now your rows will be highlighted .......
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
OK I am confused

IF(AND(A1>=0.375,A2<=0.1875),"TRUE","FALSE")

Y did u use these 2 Bold values instead i would have write it simple time , and if u did how did u get this value

Thirdly , i m using EXCEL 2007 , where to put this formula

=\$D2="Partial" What does this formula mean

Please Upload the excel for both the above example and provide me the link so that i can get an idea

PERHAPS A VERY SIMPLE EXAMPLE WITH "AND" FUNCTION WILL BE EASY TO UNDERSTAND .

I TRIED THIS HIGHLIGHT FUNCTION IT DIDNT WORK :(
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
Ok here is another solution with details ........ now u can put any date in column C2 and result will be shown in C3 ....... but keep in mind that A1 value must be less then A2 ..........

```A1= 9:00 AM
A2= 4:00 PM	C2=08:59 AM
C3=IF(AND(C2>=VALUE(A1),C2<=VALUE(A2)),"TRUE","FALSE")```
Ok I have a question!

If the value in A2 is less than or equal to 75 I want cell A1 to change to red format. Does anyone know how to do this??
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
0.375 is the value of 09:00 AM which i got from writing in Cell B1 as B1 cell = value(A1) = 0.375 where A1 cell is equal to 09:00 AM

AND function will return True if the Time will be between 09:00 AM & 04:30 PM. So when both conditions will true then True part will work else it will return False.

A1 = 9:00 AM
A2 = 4:30 PM

Formula=IF(AND(A1>=0.375,A2<=0.1875),"TRUE","FALSE")

First of all write in B1 =
value(A1) = 0.375
THEN B2= VALUE(A2) = 0.1875

Theses are the values of 09:00 AM & 04:30 am. Now you can use them in Formula. Hope you have got it ......

About =\$D2="Partial" you will have to write it in Conditional formatting box ... i've 2003 so hard to tell you about 2007.
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
u r not getting the point

this foruma which u gave me is not working

let me show u

A1:9:00 AM
A2:4:00 PM

C2: This is where when i put the data , Then i want the cell c2 to checks if the data is between range 9:00 AM and 4:00 PM if the Result is true /false i want the result to be displayed in cell C3

C3: THIS IS WHERE I WANT THE RESULT TO BE DISPLACED after i input that data in C2

now if u were my place , which formula will u put in cell C3 ?

DATA SUMMARY
A1: 9:00 AM
A2: 4:00 PM

A1:A2 IS complete data range
C2: Where i input the value that checks from value A1:A2
C3: I want the true/false result from the Value which i input in cell c2

BTW I follow that highliig the formula like " formula is " =\$A#\$1 if i remove the \$ before 1 i get two rows highlted at one.
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
One more thing

C3=IF(AND(C2>=VALUE(A1),C2<=VALUE(A2)),"TRUE","FALSE")

How can i hide the "false" when no value is entered . i want the cell to show neither true/false when no value is intered

any modification on the above formula

IF(ISNA DOSENT WORK :(
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
If A1 or A2 is empty then use this formula ......

=IF((OR(A1="",A2="")),"",IF(AND(C2>=VALUE(A1),C2<=VALUE(A2)),"TRUE","FALSE"))

About highlighting the row you should give thre reference cell as \$A1 keeping in mind that your desired result is in Cell A1 , A2 and so on ........but one thing if the same value existing in two rows then two rows will be highlighted .....

Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
Select cell A1
Go to Format
Conditional Format
Condition 1: Select Formula Is : \$A\$2<=75
Adjust the fomrat from format Tab of Condition 1

Perfect. Just the job, thanx.
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
u welcome
how to input a value of cell in second option with function if
Posts
11
Registration date
Saturday April 4, 2009
Status
Member
Last seen
December 31, 2009
1
PERFECT

FINALLY U SOLVED MY QUERY

THANK YOU VERY MUCH :)

Also one more thing

in excel 2003 or excel 2007 how i can i highlight an entire row if the value matches from the single cell in conditional formatting . i tried for example by default the formula is =\$A\$1 so i changed to = \$A1 but that shows two highlited rows at same time , which is not my desired result.

waiting