Conditional formatting for tracking training and highlight cells

[yewkey]

Hi All,

Happy New Year to all, hoping you're all well.

I am trying to create a training spreadsheet that will track training and highlight the cells with green, Amber and red accordingly when training is in date, close to expiry and expired.

The training needs to be renewed on a 6 monthly basis.

Example: if training was done today upto and including 5 months from today the cell would remain green.

From the 5th month until 1 day before expiry should turn Amber and then on the day of the 6th month be red.

Once training has been completed and the new date input should turn green again and repeat.

I hope this makes sense, I'm sure it's probably quite basic but all of my excel training has been self taught from youtube/Google so please be kind.

I'm thinking it's something with using =today but can't quite figure it out, I'm hoping your expertise will be able to help me.

Many Thanks All

Yewkey

 

[yewkey]

i'm really sorry - not sure i'm following as we are talking future dates
This should be straightforward, but i have a bit of cold this afternoon - so may not be following exactly

assuming today() 13-1 -23
OR are you entering future dates when the training is DUE and not when the training took place
What dates in the REAL training list entered in the past would turn RED
What dates in the REAL training list entered in the past would turn AMBER
What dates in the REAL training list entered in the past would turn GREEN

assuming all dates entered must be less than today() as training cannot take place in the future - UNLESS
you entering future dates when the training is DUE and not when the training took place
as i mentioned
would you mind giving another example of dates an colours - as you would enter in the real data sheet
and coloured them manually based on todays - date

No probs, hope you start feeling better soon.

All cells will be blank until the day training has taken place in which the date of that day will be entered.

I will be entering future dates as well as and when training is completed.


What dates in the REAL training list entered in the past would turn RED -Today and going forward, unless I then input today again as training completed and would then turn green and remain so for 5 months.

What dates in the REAL training list entered in the past would turn AMBER - 14/12/2022 until 12/01/2023

What dates in the REAL training list entered in the past would turn GREEN - 13/07/2022 this would of been the date input of training completed until -13/12/2022

I hope this is a little more clearer, ill create and attach another mock spreadsheet aswell.

 

[yewkey]

Book 3.xlsx

Shared with Dropbox

www.dropbox.com

Here is a mock spreadsheet of what im looking to create without any formulas in. I have coloured the cells and put a description of how it should work.

I hope this now makes more sense.

Yewkey

 

[etaf]

ok, thanks - why are you referring 13/7/23 and not talking about today() 13/1/23 (now 14/1/23) - which is what the colours would be based on , when the spreadsheet is opened - how they relate to today()

so dates in the sheet will be coloured based on today() date -
or am i really missing something
I'm finding it difficult to talk about some time in the future - .....

can you list various dates and colour them BUT based on todays date

 

[etaf]

how about


date is now the 14/1/23

Book34
	A	B	C	D	E	F
1				RED	Amber	Green
2		1/2/23		TRUE	TRUE	FALSE
3		1/4/23		TRUE	TRUE	FALSE
4		1/6/23		TRUE	TRUE	FALSE
5		1/8/23		TRUE	TRUE	FALSE
6		1/10/23		TRUE	TRUE	FALSE
7		1/12/23		TRUE	TRUE	FALSE
8		1/13/23		TRUE	TRUE	FALSE
9		1/14/23		FALSE	TRUE	FALSE
10		1/15/23		FALSE	TRUE	FALSE
11		1/16/23		FALSE	TRUE	FALSE
12		1/17/23		FALSE	TRUE	FALSE
13		1/18/23		FALSE	TRUE	FALSE
14		1/19/23		FALSE	TRUE	FALSE
15		1/20/23		FALSE	TRUE	FALSE
16		1/21/23		FALSE	TRUE	FALSE
17		1/22/23		FALSE	TRUE	FALSE
18		1/23/23		FALSE	TRUE	FALSE
19		1/24/23		FALSE	TRUE	FALSE
20		1/25/23		FALSE	TRUE	FALSE
21		1/26/23		FALSE	TRUE	FALSE
22		1/27/23		FALSE	TRUE	FALSE
23		1/28/23		FALSE	TRUE	FALSE
24		1/29/23		FALSE	TRUE	FALSE
25		1/30/23		FALSE	TRUE	FALSE
26		1/31/23		FALSE	TRUE	FALSE
27		2/1/23		FALSE	TRUE	FALSE
28		2/2/23		FALSE	TRUE	FALSE
29		2/3/23		FALSE	TRUE	FALSE
30		2/4/23		FALSE	TRUE	FALSE
31		2/5/23		FALSE	TRUE	FALSE
32		2/6/23		FALSE	TRUE	FALSE
33		2/7/23		FALSE	TRUE	FALSE
34		2/8/23		FALSE	TRUE	FALSE
35		2/9/23		FALSE	TRUE	FALSE
36		2/10/23		FALSE	TRUE	FALSE
37		2/11/23		FALSE	TRUE	FALSE
38		2/12/23		FALSE	TRUE	FALSE
39		2/13/23		FALSE	TRUE	FALSE
40		2/14/23		FALSE	FALSE	TRUE
41		2/15/23		FALSE	FALSE	TRUE
42		2/16/23		FALSE	FALSE	TRUE
43		2/17/23		FALSE	FALSE	TRUE
44		2/18/23		FALSE	FALSE	TRUE
45		2/19/23		FALSE	FALSE	TRUE
46		2/20/23		FALSE	FALSE	TRUE
47		2/21/23		FALSE	FALSE	TRUE
48		2/22/23		FALSE	FALSE	TRUE
49		2/23/23		FALSE	FALSE	TRUE
50		2/24/23		FALSE	FALSE	TRUE
51		2/25/23		FALSE	FALSE	TRUE
52		2/26/23		FALSE	FALSE	TRUE
53		2/27/23		FALSE	FALSE	TRUE
54		2/28/23		FALSE	FALSE	TRUE
55		3/1/23		FALSE	FALSE	TRUE
56		3/2/23		FALSE	FALSE	TRUE
57		3/3/23		FALSE	FALSE	TRUE
58		3/4/23		FALSE	FALSE	TRUE
59		3/5/23		FALSE	FALSE	TRUE
60		3/6/23		FALSE	FALSE	TRUE
61		3/7/23		FALSE	FALSE	TRUE
62		3/8/23		FALSE	FALSE	TRUE
63		3/9/23		FALSE	FALSE	TRUE
64		3/10/23		FALSE	FALSE	TRUE
65		3/11/23		FALSE	FALSE	TRUE
66		3/12/23		FALSE	FALSE	TRUE
67		3/13/23		FALSE	FALSE	TRUE
68		3/14/23		FALSE	FALSE	TRUE
69		3/15/23		FALSE	FALSE	TRUE
70		5/18/23		FALSE	FALSE	TRUE
71		5/20/23		FALSE	FALSE	TRUE
72		5/22/23		FALSE	FALSE	TRUE
73		5/24/23		FALSE	FALSE	TRUE
74		5/26/23		FALSE	FALSE	TRUE
75		5/28/23		FALSE	FALSE	TRUE
76		5/30/23		FALSE	FALSE	TRUE
77		6/1/23		FALSE	FALSE	TRUE
78		6/3/23		FALSE	FALSE	TRUE
79		6/5/23		FALSE	FALSE	TRUE
80		6/7/23		FALSE	FALSE	TRUE
81		6/9/23		FALSE	FALSE	TRUE
82		6/11/23		FALSE	FALSE	TRUE
83		6/13/23		FALSE	FALSE	TRUE
84		6/15/23		FALSE	FALSE	TRUE
85		6/17/23		FALSE	FALSE	TRUE
86		6/19/23		FALSE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
D2:D86	D2	=AND(B2<>"",B2<TODAY())
E2:E86	E2	=B2<DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))
F2:F86	F2	=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B91	Expression	=AND(B2<>"",B2<TODAY())	text	YES
B2:B88	Expression	=AND(B2<>"",B2<DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())))	text	YES
B2:B89	Expression	=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))	text	NO

 

[yewkey]

Hi etaf,

Thanks for your response again.

If I did training today I would put today's date in the cells 14/01/2023. I am not going to put another date in the cells until training is complete in the next six months when due. During this 6 months the cells will be green for 5 months, then amber for a month and then red until the renewal date is put in.

The next date that I'll put in for instance will be 14/07/2023 when the training is now due and complete, the cells will turn green again and the cycle will continue.

Does that make more sense with the spreadsheet that I've attached.

You may have a better idea of a way to do this rather than the way I have shown, I'm open to other suggestions.

I really appreciate all of your help with this and sorry to be such a pain.

Kind regards

Yewkey

 

[etaf]

If I did training today I would put today's date in the cells 14/01/2023. I am not going to put another date in the cells until training is complete in the next six months when due. During this 6 months the cells will be green for 5 months, then amber for a month and then red until the renewal date is put in.

rather than 6 months in future - can we talk about today and example training dates
if you had put in a date when the training takes place - based on today

if the date was 6 mths ago it would turn red - 13/7/22 would turn red today - 14/7/23 as the training was last completed 6mths ago
if the date was between 5 and 6 mths amber - 14/7/22 would turn amber as today 14/7/23 the training due tomorrow - and 14/8/22 would be amber as its 5mths old
if any date was entered less than 5 months - 15/8/22 would be green - as not due for the 6 mths

so we have 17 different people and the dates the training took place - all in the past as we cannot actuallly complete the training in the future

we now -
names 4 to 7 are coming up and need retraining as there last training dates are in the 5 month period and shown as amber
names 1 to 3 - are overdue and the last training was over six months ago - so training should have been done and the date should have been changed
Names 8 to 17 are all fine as the training is still current

Now name 17 will turn red on 14/7/23 - as now overdue and will turn amber on 14/6/23 as 5 months

Book35
	A	B
1	name	date
2	name1	5/1/22
3	name2	6/1/22
4	name3	7/13/22
5	name4	7/14/22
6	name5	7/15/22
7	name6	8/3/22
8	name7	8/14/22
9	name8	8/15/22
10	name9	9/16/22
11	name10	10/11/22
12	name11	10/25/22
13	name12	11/8/22
14	name13	11/22/22
15	name14	12/6/22
16	name15	12/20/22
17	name16	1/3/23
18	name17	1/14/23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C3,B9:C27,B4:B8	Expression	=AND(B2<=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY())),B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))	text	YES
B2:C3,B9:C27,B4:B8	Expression	=AND(B2<>"",B2<=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))	text	YES
B2:C3,B9:C27,B4:B8	Expression	=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))	text	YES

 

[yewkey]

Hi etaf,

Thanks again for your efforts and sorry I'm only just getting back, it was a busy weekend.

I have copied and pasted these formulas into a spreadsheet and checked them but it still doesn't seem to be doing what I need it to.

The dates going forward remain green and do not change colour as required.

I think I'm going to need to try a different way to keep track of the training.

Kind regards

Yewkey

 

[etaf]

NO PROBLEM
conditional formatting should work

can you give an example - based on todays date - which is the important thing as that is what excel will use today()
so it knows todays date

and then wee colour any other cell date in the spreadsheet - with a simple formula

so just a list of dates and based on today - what colour

I have done both past and future now

put in a share
in a spreadsheet a list of dates and colours
and manually colour the cells with a fill colour

you can use XL2BB or a share like dropbox

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[yewkey]

Hi etaf,

I'm at work at present, I'll have to do this tonight/tomorrow when I get home and upload as unable to do it via works system as can't access/post externally.

Yewkey

 

[etaf]

no rush, just would like to gt this right for you ... i get notified when ever you post a reply here