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

 

[etaf]

i have used 3 rules
RED
=AND(B2<>"",B2<=TODAY())
AMBER
=AND(B2<=TODAY(),B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
GREEN
=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))

and put in order Amber, Green, Red and used STOP IF TRUE

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
B2:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(B2<>"",B2<=TODAY())

Format [Number, Font, Border, Fill] FILL RED
choose the format you would like to apply when the condition is true
OK >> OK

repeat for other rules

column C,D,E,F are just for show - so you see the formula working - NOT actually NEEDED

Book26
	A	B	C	D	E	F
1	name	date	RED	AMBer	Green	months
2		5/1/22	TRUE	FALSE	FALSE	9
3		6/1/22	TRUE	FALSE	FALSE	8
4		7/1/22	TRUE	FALSE	FALSE	7
5		8/1/22	TRUE	FALSE	FALSE	6
6		9/1/22	TRUE	FALSE	TRUE	5
7		10/1/22	TRUE	FALSE	TRUE	4
8		11/1/22	TRUE	FALSE	TRUE	3
9		12/1/22	TRUE	FALSE	TRUE	2
10		12/10/22	TRUE	FALSE	TRUE	2
11		12/11/22	TRUE	FALSE	TRUE	2
12		12/12/22	TRUE	FALSE	TRUE	2
13		12/13/22	TRUE	TRUE	TRUE	1
14		12/14/22	TRUE	TRUE	TRUE	1
15		12/15/22	TRUE	TRUE	TRUE	1
16		12/16/22	TRUE	TRUE	TRUE	1
17		12/17/22	TRUE	TRUE	TRUE	1
18		12/18/22	TRUE	TRUE	TRUE	1
19		1/13/23	TRUE	TRUE	TRUE	1
20		1/14/23	FALSE	FALSE	TRUE	#NUM!
21		1/21/23	FALSE	FALSE	TRUE	#NUM!
22		1/22/23	FALSE	FALSE	TRUE	#NUM!
23		1/23/23	FALSE	FALSE	TRUE	#NUM!
24		1/24/23	FALSE	FALSE	TRUE	#NUM!
25		1/25/23	FALSE	FALSE	TRUE	#NUM!
26		1/26/23	FALSE	FALSE	TRUE	#NUM!
27		3/1/23	FALSE	FALSE	TRUE	#NUM!
Sheet1

Cell Formulas
Range	Formula
C2:C27	C2	=AND(B2<>"",B2<=TODAY())
D2:D27	D2	=AND(B2<=TODAY(),B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
E2:E27	E2	=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))
F2:F27	F2	=MONTH(TODAY()-B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Expression	=AND(B1<=TODAY(),B1>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))	text	NO
B:B	Expression	=B1>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))	text	YES
B:B	Expression	=AND(B1<>"",B1<=TODAY())	text	NO

 

[yewkey]

Hi etaf,

Thank you for your response and help.

I have tried the above but it doesn't seem to be working the way I need it to.

I may have been unclear with my initial request of what I'm looking to achieve.

Training takes place on a 6 monthly basis, if I did training today I would be valid for the next six months including today.

I want the cells to fill green today upto and including the 5th month.

From the 5th month until 1 day before training to remain amber to show it's nearing to renewal.

Red from the day of exactly six months from today to show it's expired until training has been completed again where it should turn back green.

I'm based in the UK so the dates we tend to use are dd/mm/yyyy, I don't know wether this will have had an affect as to why the formulas you gave are not working as I'm wanting.

So sorry for being a pain and greatly appreciate all the help and advice given.

Yewkey

 

[etaf]

I'm also in UK and the dates change to US version on xl2bb
whne you click on the copy - the symbol under the F(x)
you should be able to paste into a spreadsheet - and the dates should match your system, may appear as numbers and then just format as date

I have saved on dropbox

i thought thats the info i had provided

can you show where its wrong

Red for dates in row 2-5
Green for dates in row 6-12
Amber for dates in row 13 - 19

and in my example future dates are also green - but maybe not needed

I only keep on dropbox for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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've added a sample sheet onto Dropbox, how can I now share this with you?

Yewkey

 

[etaf]

put the link on here
make sure its for everyone to see - otherwise you have to add emails to validate

 

[yewkey]

Hi etaf,

Here is the link.

BookYuksel Ali's OneDriveView more actions to take on Book 10m ago.url

Shared with Dropbox

www.dropbox.com

kind regards

Yewkey

 

[etaf]

link wants me to go to onedrive - a URL from dropbox
BookYuksel Ali's OneDriveView more actions to take on Book 10m ago. .url

 

[yewkey]

BookYuksel Ali's OneDriveView more actions to take on Book 10m ago.url

 

[yewkey]

I'm copy and attaching the link from Dropbox here, what am I doing wrong?