Edate formula for blank cells is now making my conditional format colour not work

[joz110820]

Not sure that header makes sense...

So I have an edate formula in collumn N, I was getting #Value whenever I Ctrl D down on blank cells so I applied formula: =IF(M3="","",EDATE(M3,36)) which worked well. HOWEVER, I now can't apply my conditional format where I want it to highlight cells less than today's date red, it usually highlights the blank ones as well (which is what i need) but I think the formula has stopped that. Gosh I hope this makes sense, it's been a looong day.

 

[joz110820]

In what way?

Nothing changed at all

 

[Fluff]

In that case can you post some sample data, including the formulae & conditional formatting.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[joz110820]

In that case can you post some sample data, including the formulae & conditional formatting.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I noticed that, I'm on a work laptop which usually restricts me from installing stuff - I'll see if it works and if not grab my personal laptop

 

[joz110820]

names etc removed.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Start Date	Orientation to B/F policy	Initial Training	Unicef Training	Attended 1 day update	Attended Practical skill review	Latest Training	Next due	Booked?	Turas
2	01/10/2004				21/06/2022	JP	21/06/2022	21/06/2025		06/06/2022
3	04/01/2022	27/04/2022							08/03/2023
4	19/11/2018				21/06/2022	JP	21/06/2022	21/06/2025		20/06/2022
5	02/07/2012					2020
6	11/05/2020				21/06/2022	JP	21/06/2022	21/06/2025
7	13/11/2006					2020
8	19/03/2007					2020
9	01/10/2004					2020
10	01/04/2010				21/06/2022	JP	21/06/2022	21/06/2025
11	01/10/2004				17/09/2020	2020	17/09/2020	17/09/2023
12	29/10/2014				09/06/2022	JP	09/06/2022	09/06/2025
13	13/07/2015					2020
14	01/04/2010				21/06/2022	JP	21/06/2022	21/06/2025
15	02/05/2022	17/05/2022			09/06/2022	JP	09/06/2022	09/06/2025		05/05/2022
16	14/05/2017					2019			16/11/2022
17	18/08/2015					2021
18	01/10/2004				12/01/2021	2021	12/01/2021	12/01/2024
19	01/04/2016				21/06/2022	JP	21/06/2022	21/06/2025
20	18/04/2022	27/04/2022	01/07/2021			In Grampion	01/07/2021	01/07/2024
21	26/09/2022		14/04/2021			2021	14/04/2021	14/04/2024
22	27/04/2020	27/04/2022							08/03/2023
23	01/10/2004					2020
24	01/10/2004				12/05/2022	JP	12/05/2022	12/05/2025
25	14/09/2015					2020
26	09/04/2012				12/05/2022	07/07/2020	12/05/2022	12/05/2025
27	24/04/2017					2021
28	31/10/2016				14/04/2021	2021	14/04/2021	14/04/2024
29	09/05/2016				18/08/2022	2019	18/08/2022	18/08/2025
30	20/02/2012					2019			16/11/2022
31	13/07/2015					2019
32	05/11/2007				12/05/2022	JP	12/05/2022	12/05/2025
33	08/09/2004					2020
34	24/09/2018				17/09/2020	2020	17/09/2020	17/09/2023
35	01/03/2004				21/06/2022	JP	21/06/2022	21/06/2025		21/06/2022
36	23/01/2017				12/05/2022	07/07/2022	12/05/2022	12/05/2025		10/05/2022
37	04/12/2006					2018
38	31/07/2006					2020
39	25/01/2019				12/05/2022	JP	12/05/2022	12/05/2025
40	06/12/2010				17/09/2020	2020	17/09/2020	17/09/2023
41	02/09/2015				15/12/2020	2020	15/12/2020	15/12/2023
42	16/04/2012				12/05/2022	27/06/2022	12/05/2022	12/05/2025
43	13/04/2012				12/01/2021	2021	12/01/2021	12/01/2024
44	29/07/2013				12/05/2022	JP	12/05/2022	12/05/2025
45	06/11/2014				21/06/2022	JP	21/06/2022	21/06/2025
46	06/04/2009				12/05/2022	JP	12/05/2022	12/05/2025
47	09/09/2015				09/06/2022	JP	09/06/2022	09/06/2025
48	22/05/2017					2021
49	01/04/2010					2019
50	29/09/2014				12/05/2022	JP	12/05/2022	12/05/2025
51	04/10/2004				12/01/2021	2021	12/01/2021	12/01/2024
52	19/04/2016				21/06/2022	JP	21/06/2022	21/06/2025
53	07/03/2018				12/01/2021	2021	12/01/2021	12/01/2024
54	07/11/2011					2021
55	13/03/2020				21/06/2022	JP	21/06/2022	21/06/2025
56	01/10/2004				12/05/2022	JP	12/05/2022	12/05/2025
57	02/09/2015					unknown
58	05/09/2016				21/06/2022	JP	21/06/2022	21/06/2025		27/05/2022
59	01/10/2004					2018
60	24/09/2007				09/06/2022	07/07/2022	09/06/2022	09/06/2025		17/05/2022
61	20/06/2005				09/06/2022	un known	09/06/2022	09/06/2025
62	18/02/2013				15/12/2020	2020	15/12/2020	15/12/2023
63	03/06/2019					un known
64	04/04/2005				28/09/2020	2020	28/09/2020	28/09/2023
65	16/08/2010				21/06/2022	JP	21/06/2022	21/06/2025		08/06/2022
66	05/09/2022	06/09/2022		QMU
67	01/09/2016				12/01/2021	2021	12/01/2021	12/01/2024
68	01/04/2010				12/01/2021	2021	12/01/2021	12/01/2024
69	31/01/2005					2020
70	01/10/2004					2018
71	01/10/2014
72	09/09/2019				21/06/2022	JP	21/06/2022	21/06/2025		20/06/2022
73						2018
74	11/12/2017				12/05/2022	2020	12/05/2022	12/05/2025
75	15/09/2014				12/05/2022	JP	12/05/2022	12/05/2025
76	01/10/2004					2020
77	05/09/2022	06/09/2022		QMU		2020
78	08/10/2007
79	01/07/2013					2020
WL HV

Cell Formulas
Range	Formula
G2:G79	G2	=IFNA(IFS(C2>E2,C2,C2<E2,E2),"")
H2:H79	H2	=IF(G2="","",EDATE(G2,36))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H:H	Expression	=OR(H2="",H2<=TODAY())	text	NO
H:H	Cell Value	<TODAY()	text	NO
I:K	Cell Value	between TODAY() and TODAY()+365	text	NO

 

[Fluff]

Thanks for that, you haven't applied a format for the rule using the formula, try editing it & then apply a format of your choice.