need help

[bam12]

I need some help with this but not sure how to explain it

I have to check a cell to see if ti has a date in it, if so , then i have to check the current date to another cell, if the current date is greater then that cell i want to change the color of the cell

Not sure what formula to use

 

[bam12]

more

column h is the projected file completion date
column g is the date the file came in
column i is the actual completion date

column h always has a date in it

so if no date in column g then dont want to continue formula or process
if it has a date in it i want to check column i for date if no date in column i want to check current date to column h

if current date is greater then date in column H, change color of cell

 

[mordrid]

Why do you want to enter code, if you try conditional formatting then follow the instruction sthack99 gave you re click format etc. You can then either use his formula or try my suggestion for cell value is. One thing though if you want to check against todays date then you can use Today() which is a standard excel function so you do not need to put it in a cell anywhere. So if you use my suggestion of cell value etc then =Today() is what you enter in the box allowed for the value (far right hand box on the condition statement.

If you have trouble understanding this then please look in the excel help files for an explanation of conditional formatting and all may become clear

Richard

 

[sthack99]

Let me se if you understand....

You can enter a value into a cell and still have a formula in conditional formatting. Hold on - let me see if I can show you what I mean....

 

[sthack99]

Sorry - I meant to say "Let me see if I understand"...

I'm going to see if I can post screen shots. Give me a sec...

 

[sthack99]

As you see, to the left you have the dates, and then somewhere in the workbook you need today's date. You can even paste it in a hidden column if you want, so it's not visible.



Select the 1st cell w/ a date in it, click Format then Conditional Formatting.



Select "Formula Is" and type in your formula. MAKE SURE YOU PUT A DOLLAR SIGN IN FRONT OF THE G & 1 (OR WHATEVER CELL TODAY'S DATE IS IN).



Then click the Format button. A pop up screen will appear, and click the Patterns tab. Select the color you want, and click ok.



Copy the cell that you applied the conditional format to, select all the other cells, right click, paste special, and choose Formats.



And, as you see, all of the cells w/ dates lower than today's date is now yellow. If you are dealing w/ blank cells, you'll need to adjust the formula to =AND(IF(A1<font color="red">insert less than symbol here</font>$G$1,TRUE,FALSE),IF(A1="",FALSE,TRUE)).

 

[sthack99]

"so if no date in column g then dont want to continue formula or process
if it has a date in it i want to check column i for date if no date in column i want to check current date to column h

if current date is greater then date in column H, change color of cell

So only column H will be changing color....I think I gotcha now...

 

[sthack99]

Here's your solution. Apply this conditional formatting formula to every cell in column H.

=IF(G1="",FALSE,AND(IF(I1="",TRUE,FALSE),IF(H1<TODAY(),TRUE,FALSE)))

 

[bam12]

help

tried both examples and dont work like i want them too

 

[mordrid]

Have you tried the example I gave earlier using cell value is instead of the formula version. When I used it for a cell with a date in it i could not get a formula version working either

 

[bam12]

YES

Yes i did

i cant use today() in any of the cells i mentioned