Forum Discussion
Conditional Formatting || Formula
- Sep 21, 2021
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.
ā
Why this isn't working, any Idea ?
Is it because the cell I am giving the conditional formatting is empty ? but again one more condition I have provided right ? Don't know why it isn't working.
arunrj Change "applies to" to $N$3
- arunrjSep 21, 2021Brass Contributor
Riny_van_Eekelen The idea is I am having a set of dates, say column M- Baseline values, column N- Estimated values & column O is the actual.
So I already have values in my M & N, whenever the estimate is crossed today & I have no value in O, I want O to be highlighted.- Riny_van_EekelenSep 21, 2021Platinum Contributor
arunrj Sorry, your intentions weren't clear to me. Change the "Applied to" to something like $O$3:$O$100. Then, with the CF formula you tried earlier all blank cells in O3:O100 will be highlighted if todays date has passed the date in column N (on the same row).
- arunrjSep 21, 2021Brass Contributor
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 ?
- arunrjSep 21, 2021Brass ContributorRiny_van_Eekelen Again how to apply the formatting to entire column O ?
What I do every time is literally copy paste the same down, but its a lot of time consuming.
I don't know to do macros, or any other way is there to do it in a simple way ?