Conditional formatting for tracking training and highlight cells

yewkey

New Member
Joined
Jan 12, 2023
Messages
16
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
  2. Mobile
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
 
i dont know
Excel Formula:
https://www.dropbox.com/s/q0kzhbwuqa25ffo/blank-etaf.xlsx?dl=0
is a dropbox link to an excel file called blank-etaf
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
what happens if you click on the link you posted
i get
Excel Formula:
BookYuksel Ali's OneDriveView more actions to take on Book 10m ago.url
 

Attachments

  • Screenshot 2023-01-13 at 16.06.45.png
    Screenshot 2023-01-13 at 16.06.45.png
    69.3 KB · Views: 4
Upvote 0
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.
 
Upvote 0
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
Excel Formula:
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
 
Upvote 0
I'm hoping that this will work now.

Thanks again.
 
Upvote 0
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
ABCD
1Dates
21/13/23this should be green as in date and should remain green for the next 5 months
36/13/23this should be amber as coming close to expiry
47/14/23this should be red as expired
51/10/23
61/8/22
7
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B16Expression=AND(B2<TODAY(),B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))textYES
B2:B16Expression=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))textNO
B2:B16Expression=AND(B2<>"",B2<=TODAY())textNO
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top