Edate Help ?

[davidmor]

Hi

i have a sheet which im making for keeping track of FLT refresher dates, i have it so it highlights using icon sets,

i would also like to be able to have it count the red/amber/ green to display on a front Sheet,

ive read that you cant use icon sets to count them,

would i need to create a hidden cell to put some data to enable excel to count each one ?

Thanks in advance for any help

FLT Training log New.xlsx
	A	B	C	D	E
1	Name	Department	Reach Truck Test Date	Reach Truck Expiry Date	Certificate Link
2		Cutting	15/10/2018	15/10/2021
3		Cutting	23/03/2018	23/03/2021
4		Cutting	12/11/2018	12/11/2021
5		Finishing	21/11/2016	21/11/2019
6		Ink Room	03/05/2016	03/05/2019
7		Print	26/04/2019	26/04/2022
8		Print	10/11/2021	10/11/2024
9		Print	11/11/2016	11/11/2019
10		Print	13/06/2019	13/06/2022
11		Print	31/10/2019	31/10/2022
12		Print	29/07/2015	29/07/2018
13		Print	10/11/2021	10/11/2024
14		Print	07/11/2022	07/11/2025
15		Print	11/12/2019	11/12/2022
16		Print	19/01/2018	19/01/2021
17		Print	25/06/2019	25/06/2022
18		Print	07/11/2022	07/11/2025
19		Print	10/11/2021	10/11/2024
20		Print	15/10/2018	15/10/2021
21		Print	19/08/2019	19/08/2022
22		Warehouse	12/11/2021	12/11/2024
23		Warehouse	23/03/2017	23/03/2020
24		Warehouse	11/10/2019	11/10/2022
25		Warehouse	21/02/2020	21/02/2023
26		Warehouse	10/01/2023	10/01/2026
27		Warehouse	27/06/2017	27/06/2020
28		Warehouse	15/06/2017	15/06/2020
29		Warehouse	23/03/2017	23/03/2020
30		Warehouse	13/06/2019	13/06/2022
31		Warehouse
32		Warehouse	28/11/2018	28/11/2021
33		Warehouse	15/06/2017	15/06/2020
34		Warehouse	14/11/2017	14/11/2020
35		Warehouse	21/11/2017	21/11/2020
HRT

Cell Formulas
Range	Formula
D2:D30,D32:D35	D2	=EDATE(C2,12*3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D35	Other Type	Icon set		NO
D2:D34	Other Type	Icon set		NO

 

[awoohaw]

@davidmor , what is the formula you use to determine the icon set color? That is the formula you need to put in your hidden column.

 

[Fluff]

How about (I have manually highligted the green & amber icons as they do not show using XL2BB)

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Department	Reach Truck Test Date	Reach Truck Expiry Date	Certificate Link
2		Cutting	43388	44484
3		Cutting	43182	44278				Red	7
4		Cutting	43416	44512				Amber	15
5		Finishing	42695	43790				Green	11
6		Ink Room	42493	43588
7		Print	43581	44677
8		Print	44510	45606
9		Print	42685	43780
10		Print	43629	44725
11		Print	43769	44865
12		Print	42214	43310
13		Print	44510	45606
14		Print	44872	45968
15		Print	43810	44906
16		Print	43119	44215
17		Print	43641	44737
18		Print	44872	45968
19		Print	44510	45606
20		Print	43388	44484
21		Print	43696	44792
22		Warehouse	44512	45608
23		Warehouse	42817	43913
24		Warehouse	43749	44845
25		Warehouse	43882	44978
26		Warehouse	44936	46032
27		Warehouse	42913	44009
28		Warehouse	42901	43997
29		Warehouse	42817	43913
30		Warehouse	43629	44725
31		Warehouse
32		Warehouse	43432	44528
33		Warehouse	42901	43997
34		Warehouse	43053	44149
35		Warehouse	43060	44156
Source

Cell Formulas
Range	Formula
I3	I3	=COUNTIFS(D2:D35,">="&(MAX(D2:D35)-MIN(D2:D35))*0.67+MIN(D2:D35))
I4	I4	=COUNTIFS(D2:D35,">="&(MAX(D2:D35)-MIN(D2:D35))*0.33+MIN(D2:D35),D2:D35,"<"&(MAX(D2:D35)-MIN(D2:D35))*0.67+MIN(D2:D35))
I5	I5	=COUNTIFS(D2:D35,"<"&(MAX(D2:D35)-MIN(D2:D35))*0.33+MIN(D2:D35))
D2:D30,D32:D35	D2	=EDATE(C2,12*3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D35	Other Type	Icon set		NO