Excel IF AND question

  • Thread starter Thread starter Yolanda
  • Start date Start date
Y

Yolanda

I need to place a formula that says: If H8's date is past, and I8 is blank,
turn cell RED. Can someone tell me how to enter this formula?
 
Use Conditional Formatting...

Enter the conditions and chose the formatting you want.
first will be H8<today()
and second I8=""
 
Thank you Sheeloo, but I am not familiar with EXCEL at all. Could you tell
me exactly how I would enter the formula? Is it: =IF H8<today()ANDi8=''''
(cell background turns RED?????
 
In my xl03, I'd do the CF this way

Assume that I want the CF to colour both cols H & I red
if the criteria stated is fulfilled

Select H8:I20 (with H8 active)
Click Formatting > Conditional Formatting
Formula Is: =AND(ISNUMBER($H8),$H8<TODAY(),$I8="")
Click Format button > Patterns tab > Red > OK
Click OK

The additional ISNUMBER check on col H is to ensure that blank cells in col
H (if any) will not spuriously trigger the CF. Blank cells are evaluated as
zeros in formulas, which is equivalent to a date of 0-Jan-1900.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
 
See my earlier response to you. Complete CF steps are given, and with a
suggested additional ISNUMBER check on col H to avoid spurious trigger due to
blank col H cells.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
 
Max, this is great, the formula is in, but the cell won't turn red when I
enter a date that is before today's date.

I want only the H column to turn RED if past due, and YELLOW if within the
next 5 days due...what do you mean by select H8 to I20 with H8 active?
 
Yolanda said:
Max, this is great, the formula is in, but the cell won't turn red when I
enter a date that is before today's date.
Click to expand...

Ensure that your date entered is a real date recognized by Excel.
It won't work if its not a real date

To find out what is the correct way to enter a real date,
just put in any cell: =TODAY()
then widen the col width to see how it appears
I want only the H column to turn RED if past due,
and YELLOW if within the next 5 days due...
Click to expand...

Ok, this requires us to only select the range in col H,
and use a 2nd CF condition

Select H8:H20 (Ensure H8 is the active cell)

Apply CF, using Formula Is:
Condition 1: =AND(ISNUMBER($H8),$H8<TODAY(),$I8="")
Format: Red Fill

Condition 2: =AND($H8>=TODAY(),$H8<=TODAY()+5,$I8="")
Format: Yellow Fill

Click to OK out
.. what do you mean by select H8 to I20 with H8 active?
Click to expand...

That basically just means to ensure that you start your selection from cell
H8, then swipe down to I20. There are actually 4 ways to select any
rectangular range, ie you can start by selecting from any of the 4 corner
cells, then swipe to the diagonally opposite corner.

As the CF formula is structured for the active cell in the selection, it is
important to ensure that the correct corner cell is the active cell.

Eg If you select I20, then swipe to H8, I20 will be the active cell. And if
you then proceed to implement the CF formulas meant for H8 as active cell,
then of course it won't work.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
 
Back
Top