Conditional Formatting / Carpark Excel Spreadsheet Set up

[Andyg666]

Hi,
I am trying to set up a spreadsheet that will help me manage our work carpark. We have set up a new policy to say that registered users will get three warnings if they park inconsiderably or illegally but I need a way to monitor the number of warnings issued to each user of the carpark and then they cant use the carpark. (The three strikes would run over a 12 month period)
Visitors will be monitored separately via ANPR

I have was thinking of using conditional formatting where if there were three warnings logged against a user then the row would change 1 warning = Green, 2 warnings = Orange, 3 Warnings = Red

Is there a way of using conditional formatting to achieve this or is there a better way that you guys could help me set up?

Example of file link > Carpark Sheet.xlsx

Thanks in advance.

 

[etaf]

i cannot access your spreadsheet
BUT yes you should be able to do that with a countifs()
using a date to go back 1 year or 365 days
and also the reg number
and then count the warnings
and setup 3 rules
count of 1 = Green
count of 2 = Orange
count of 3 = red

 

[Andyg666]

i cannot access your spreadsheet
BUT yes you should be able to do that with a countifs()
using a date to go back 1 year or 365 days
and also the reg number
and then count the warnings
and setup 3 rules
count of 1 = Green
count of 2 = Orange
count of 3 = red

Thanks I will see what I can do. I have attached a screen shot of the currently very simple file

 

[etaf]

i have used this formula , to only count dates later than 365 days ago
=COUNTA(FILTER(--TEXTSPLIT(D4," ",,,1),--TEXTSPLIT(D4," ",,,1)>=TODAY()-365))
you can see in column G

maybe an easier formula - but thats wht i have based on your data structure

then i use 3 rules in condition formatting for each fill colour

=COUNTA(FILTER(--TEXTSPLIT($D4," ",,,1),--TEXTSPLIT($D4," ",,,1)>=TODAY()-365))=1
=COUNTA(FILTER(--TEXTSPLIT($D4," ",,,1),--TEXTSPLIT($D4," ",,,1)>=TODAY()-365))=2
=COUNTA(FILTER(--TEXTSPLIT($D4," ",,,1),--TEXTSPLIT($D4," ",,,1)>=TODAY()-365))=3

Book4
	A	B	C	D	E	F	G	H
1
2
3		name	reg	warnings			Valid warnings
4		fred1	1234	1/1/24 1/2/24 1/3/24			3
5		fred2	1235	1/1/22 1/2/22 1/3/24			1
6		fred3	1236	1/1/24			1
7		fred4	1237	1/1/24 1/2/24			2
8		fred5	1238	1/1/20 1/2/24 1/3/24			2
9		fred6	1239	1/1/20 1/2/20 1/3/20			1
10
11
Sheet2

Cell Formulas
Range	Formula
G4:G9	G4	=COUNTA(FILTER(--TEXTSPLIT($D4," ",,,1),--TEXTSPLIT($D4," ",,,1)>=TODAY()-365))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:D9	Expression	=COUNTA(FILTER(--TEXTSPLIT($D4," ",,,1),--TEXTSPLIT($D4," ",,,1)>=TODAY()-365))=1	text	NO
B4:D9	Expression	=COUNTA(FILTER(--TEXTSPLIT($D4," ",,,1),--TEXTSPLIT($D4," ",,,1)>=TODAY()-365))=2	text	NO
B4:D9	Expression	=COUNTA(FILTER(--TEXTSPLIT($D4," ",,,1),--TEXTSPLIT($D4," ",,,1)>=TODAY()-365))=3	text	NO

does everyone appear on the list - regardless of warnings

is that a true representation of the data you get ?