Conditional Formatting and IF Statement

[stjoe]

I need some assistance with using IF statment and Conditional Formatting.

In cell C19 I want to enter a date of < = 1/1/1993
I want my cell L19 to be grey out

In Cell C19 I want to enter a date of < = 1/1/2005
I want my cell L19 to be grey out

In both senarios if the date is not less than or equal to the date I enter I want the cell to remain white.

I have tried conidtional formating and If statements and non are working to what I need.


Please Help.

 

[bRicaNz]

you mean if you input less than or equal to 1/1/1993 in M19 the L19 will be fill with grey color. Then if your input less than or equal to 1/1/2005 in M19 the L19 will be also be fill with grey color. is that what you mean?

If that so, I guess you have problem with your if statement....

 

[stjoe]

Sorry the question is as follows: They reference two different cells that need to be grey out



In cell C19 I want to enter a date of < = 1/1/1993
I want my cell L19 to be grey out

In Cell C19 I want to enter a date of < = 1/1/2005
I want my cell J19 to be grey out

In both senarios if the date is not less than or equal to the date I enter I want the cell to remain white.

 

[Murali1881]

Try this formula on the Conditional Formatting....

=IF($C$19<=33970,TRUE,FALSE) on cell L19

=IF($C$19<=38353,TRUE,FALSE) on cell J19

Apply the format to desired color ie, GREY.

Thanks & Regards,

Murali

 

[stjoe]

Thanks I will try this but did u convert the date if so how? I need to change the date to be 12/31/2004 and 12/31/1992 how would I do this?

 

[stjoe]

Also do I enter the formula in cell L19 using conditional formatting? Or do I enter the formula in my cell where I enter the hire date?

Can u send a screen shot of where to enter the formula?

Thanks

 

[MisterBate5]

The value is how Excel internally stores dates/times - with the date part to the left of the decimal (whole number), the time part to the right (fractional).

To see what value excel uses for a specific date, use =datevalue("1993-01-01"). You can even replace the 33970 with the davevalue() in your formula to make it clearer which date that is. Similar with the "2005-01-01" date. BTW, using yyyy-mm-dd is international ISO std for date format and should work wherever your sheet will be used, independent of the regional settings for date format.

You enter the conditional format in the cell that you want shaded, and depending on your excel version (pre-2007 or 2007 on) will have a different dialog to fill. Conditional format formulae are entered into a special dialog, rather than being entered in the cell. Important thing is that the format will be applied if the formula evaluates to true, so the formulae given below can be simplified to:

=$C$19<=datevalue("1993-01-01") on cell L19
=$C$19<=datevalue("2005-01-01") on cell J19

 

[stjoe]

This is not working. When I put in the formula in cell J19 and L19 then to my c19 where i enter the hire date the both cell turn white. based on the date I enter I want the cell to turn either white or grey. Help.

 

[stjoe]

When I take out the $ from $L$19 to $L19 and try to copy this formula down it doesnt work. What should I use if I want to apply this formula down to $L$19 thr L100?

 

[MisterBate5]

1) Did you put the formulae in the cell? or apply them as the formulae for conditional formatting using the cond format dialog?
2) The formulae will return TRUE when the condition is met, and it's this TRUE that alters the format of the cell. What have you told conditional formatting to do when the formaula returns true - shade grey? shade white?

... and why are you working with a formula that has L19 in it? The conditional formulae refer *only* to cell $c$19 as that's the cell you want to test. It sounds like you have data in rows, in which case, in column J set the conditional format formula to shade grey with formula

=$C19<=datevalue("2005-01-01")

and in column L set conditional format to shade grey with formula

=$C19<=datevalue("1993-01-01")