Conditional Formatting of a formula based date

[Liam2003]

Hi,

I am having problems with conditional formatting of a formula based date. My perfectly working formula in the cell that I wish to conditional format is {=IFERROR(INDEX(contracts_data,MATCH(1,IF(Contract_No=$AU$5,IF(contract_itemcode=$C11,1)),0),4),"")}, this formula works perfectly but the conditional format seems like it is not picking up the date. My conditional format is Cell Value >Today(). (if the date in this cell is greater than today, then apply the format) Any help on this would be very much appreciated. Thank you in advance.

 

[awoohaw]

why not just use this as your CF rule:

=INDEX(contracts_data,MATCH(1,IF(Contract_No=$AU$5,IF(contract_itemcode=$C11,1)),0),4)>Today()

 

[Liam2003]

=INDEX(contracts_data,MATCH(1,IF(Contract_No=$AU$5,IF(contract_itemcode=$C11,1)),0),4)>Today()

Thank you for your suggestion, but it is doing the same thing, it is applying the format to dates it should'nt be. ??

 

[kevin9999]

Instead of using the Cell Value/Greater than option in CF, use the Use a formula to determine which cells to format - with the formula:

=AP11>Today()

Book1
	AO	AP	AQ
10
11		31/12/2023
12		31/10/2024
13
14
15
16
17
18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AP11:AP25	Expression	=AP11>TODAY()	text	YES

 

[Liam2003]

Instead of using the Cell Value/Greater than option in CF, use the Use a formula to determine which cells to format - with the formula:

=AP11>Today()

Book1
	AO	AP	AQ
10
11		31/12/2023
12		31/10/2024
13
14
15
16
17
18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AP11:AP25	Expression	=AP11>TODAY()	text	YES

Hi Kevin,

Tried this before and still not formatting, I have a feeling that the indexing formula in this cell is the problem. But Thank you anyway

 

[Alex Blakenburg]

If you change the formatting in column AP do the dates change how they display (if you pick something like general it should be really obvious if they change or not) Ctrl+Z to reset the format back again.
Assuming they don't change can you show us the original data where you are getting the date from and tell us were the data is coming from and if there is a formula there as well what is that formula ?
Does this fix it ?

=DateValue(AP11)>Today()

 

[awoohaw]

Thank you for your suggestion, but it is doing the same thing, it is applying the format to dates it should'nt be. ??

I'm not really sure what you mean. If you put that formula in the CF rule, it will only format cells where that formula resolves to TRUE. If you can post an xl2bb mini workbook of the cells with the CF, as well as the cells that the CF rule reference then the forum can better help you.

 

[Liam2003]

Thank you all very much for the valuable information you have provided, it has been very much appreciated. I have just found where the problem is, it was the format of the date in the database downloaded from IMAS.

 

[Alex Blakenburg]

That seemed the most likely given that the other suggestions didn't work.
Thanks for letting us know. Glad we could help.