Forum Discussion
Conditional Formatting || Formula
arunrj Oh my! You have real dates in M and then you created texts from them in N if there is no date entered in column O. Obviously, you can't apply date calculations on texts. Use a formula like:
=IFERROR(DATEVALUE(IF(ISNUMBER(O3),"NA","09/20/2021")),"NA")
in column N. This will create a real date that you test with the CF formula. But I wonder why you would go through all the trouble of entering the estimate date with such a formula. That's your choice, of course.
Riny_van_Eekelen But the formula is for that particular cells only, right ? How can I apply to say O3:O100 ?
It will be like when O3 & N3 values in formula will deicide for entire O column, right ?
arunrj In conditional formatting, you write the formula as with references to cells in one single row. In your case comparing N3 and O3. Applying that rule to a range O3:O100 will tell Excel to apply the same rule to all cells in that range. So, in O4 the rule will look at N4 and O4. In O5, it will look at N5 and O5 and so on. Similar to when you enter a formula with relative references and copy it down a column.
Have you tried it? Otherwise, upload a schedule that resembles your real one and I'll put the rule(s) in it for you.
Riny_van_Eekelen Actually, i am having a formula in the N column, which is why I think my conditional formatting not working. I am attaching my worksheet.
Can I accommodate this CF with the existing one in sheet attached ?
arunrj Oh my! You have real dates in M and then you created texts from them in N if there is no date entered in column O. Obviously, you can't apply date calculations on texts. Use a formula like:
=IFERROR(DATEVALUE(IF(ISNUMBER(O3),"NA","09/20/2021")),"NA")
in column N. This will create a real date that you test with the CF formula. But I wonder why you would go through all the trouble of entering the estimate date with such a formula. That's your choice, of course.
Riny_van_Eekelen : Thanks for a solution, but If I just add the DATEVALUE in CF, it will be fine right ?
like =AND(ISBLANK(O3),TODAY()>DATEVALUE(N3))
Rather than changing the formula again, as it won't serve my purpose.