SOLVED

Conditional Formatting || Formula

Brass Contributor

Can I use this formula =AND(ISBLANK(A2),TODAY()>A1) for conditional formatting, if I want to highlight A1 ?

My requirement is to highlight the content of A1 (date) if value in A2 (date) is not available & A1(date) is in the past.

 

How can I address this condition using formula ? or there is any other way to do this ?

 

Please help 

14 Replies

@arunrj The formula shall work. Not sure what your problem is. Have you tried it?

Attached is a working example. 

@Riny_van_Eekelen Thanks for the response, yea, it worked

 

Query.JPG

 

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

@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_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 ?

@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).

 

 

 

 

 

@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 ?

 

 

best response confirmed by allyreckerman (Microsoft)
Solution

@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.

 

@arunrj Just try it!

yea, its working, thanks
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@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.

View solution in original post