want to leave 00/jan/00, does not want to apply conditional formatting

[learning_grexcel]

I have a column with lot of dates and in blank cell the date is shown as "00/jan/00" as these dates are formulas which got from another sheet.

I put a conditional formatting if it is now(), it should mark red, but it marks even that 00/jan/00 cell as red. I don't know if I can change it to 0 but at least I'm looking for it should not mark as red, it should leave as it is.

 

[barry houdini]

If those are real dates then try using a custom format on that column as follows:

dd/mmm/yy;;

Not the two semi-colons at the end - that should make "zero dates" show as blanks.....

I'm not sure how those got formatted with your conditional formatting - how did you use conditional formatting exactly?

 

[learning_grexcel]

I changed date format as you said but still the red color didn't change though value changed as "blanks".
Actually I went to "conditional formatting" >> "Manage rules" >> format only cells that contain >> cell value --less than -- now ()

 

[barry houdini]

well those "dates" are effectively zero.....which is less than =NOW()

Try changing to "between" option, between 1 and NOW().....or use a formula. If column of dates is M then select column and use this formula in conditional formatting

=AND(M1< NOW(),M1>0)