Sep 16 2021 02:23 AM
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
Sep 16 2021 02:53 AM
@arunrj The formula shall work. Not sure what your problem is. Have you tried it?
Attached is a working example.
Sep 17 2021 11:27 PM
Sep 20 2021 05:08 AM
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.
Sep 20 2021 09:05 PM - edited Sep 20 2021 09:06 PM
@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.
Sep 20 2021 09:11 PM
Sep 20 2021 10:06 PM
@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).
Sep 20 2021 10:11 PM
@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 ?
Sep 20 2021 10:22 PM
@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.
Sep 20 2021 10:38 PM
@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 ?
Sep 21 2021 12:01 AM
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.
Sep 21 2021 01:27 AM
@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.
Sep 21 2021 12:01 AM
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.