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 dont know

https://www.dropbox.com/s/q0kzhbwuqa25ffo/blank-etaf.xlsx?dl=0

is a dropbox link to an excel file called blank-etaf

 

[yewkey]

The sheet is just called book, I've copied and paste the link from Dropbox after putting the sheet in there if that makes sense.

 

[etaf]

what happens if you click on the link you posted
i get

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

 

[yewkey]

At present I'm out, on my phone it says " .URL files can't be previewed. To open this file try a computer or another app."

I dragged and dropped the sheet into Dropbox and then copied and pasted the link to it here.

Sorry for all of these problems, thank you for your help.

 

[etaf]

no problem
i went into dropbox - online via a browser (i have it as a share on PC , and normally get a link from there)
then on the right i have
copy link
which i clicked on
and then this is the link

https://www.dropbox.com/s/q0kzhbwuqa25ffo/blank-etaf.xlsx?dl=0

which is the same as i get from my PC share

where as you get a URL
with a name that does not look like an excel name

 

[yewkey]

Book.xlsx

Shared with Dropbox

www.dropbox.com

 

[yewkey]

I'm hoping that this will work now.

Thanks again.

 

[etaf]

so for amber I had
=AND(B2<=TODAY(),B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
where it equals today()
so change to
=AND(B2<TODAY(),B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
that now ignores the date if equal to today

OK, so we are looking at dates in the future NOT in the past

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

not sure why the date 13/06/2023 in 5 months time is amber as its

this should be amber as coming close to expiry

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

are you entering dates in the future ?

14/7/23 - in 6mths time has expired

I may just be completely misunderstanding the dates - entered and coloured

would you mind giving another example of dates an colours - as you would enter in the real data sheet

I just assumed all the dates would be entered when the training actually took place and so would be on the past
and so if the training was was greater 6mths ago - 14/7/22 - would be red as i day before 6 mths old

in your example you are saying that RED is 6mths in the future - 14/7/23
sorry if i'm misunderstanding this , BUT the drop box link worked OK

Book.xlsx
	A	B	C	D
1		Dates
2		1/13/23	this should be green as in date and should remain green for the next 5 months
3		6/13/23	this should be amber as coming close to expiry
4		7/14/23	this should be red as expired
5		1/10/23
6		1/8/22
7
Sheet1

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

 

[yewkey]

Hi etaf,

Thanks again and glad you could open it this time.

Your understanding correctly.

So if training was done today, today's date would be entered as completed, this cell will remain green for the next 5 months proceeding today.

From the beginning of the 6th month until 1 day before expiration should be amber.

Then on the day in which the training needs to be renewed should be red and stay red until training is completed e.g. 13/07/2023 would be red.

If training was then completed on 14/07/2023 it would turn green again and be the same for the following 6 months.

 

[etaf]

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