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'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.
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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
 
Upvote 0
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
 
Upvote 0
how about


date is now the 14/1/23

Book34
ABCDEF
1REDAmberGreen
21/2/23TRUETRUEFALSE
31/4/23TRUETRUEFALSE
41/6/23TRUETRUEFALSE
51/8/23TRUETRUEFALSE
61/10/23TRUETRUEFALSE
71/12/23TRUETRUEFALSE
81/13/23TRUETRUEFALSE
91/14/23FALSETRUEFALSE
101/15/23FALSETRUEFALSE
111/16/23FALSETRUEFALSE
121/17/23FALSETRUEFALSE
131/18/23FALSETRUEFALSE
141/19/23FALSETRUEFALSE
151/20/23FALSETRUEFALSE
161/21/23FALSETRUEFALSE
171/22/23FALSETRUEFALSE
181/23/23FALSETRUEFALSE
191/24/23FALSETRUEFALSE
201/25/23FALSETRUEFALSE
211/26/23FALSETRUEFALSE
221/27/23FALSETRUEFALSE
231/28/23FALSETRUEFALSE
241/29/23FALSETRUEFALSE
251/30/23FALSETRUEFALSE
261/31/23FALSETRUEFALSE
272/1/23FALSETRUEFALSE
282/2/23FALSETRUEFALSE
292/3/23FALSETRUEFALSE
302/4/23FALSETRUEFALSE
312/5/23FALSETRUEFALSE
322/6/23FALSETRUEFALSE
332/7/23FALSETRUEFALSE
342/8/23FALSETRUEFALSE
352/9/23FALSETRUEFALSE
362/10/23FALSETRUEFALSE
372/11/23FALSETRUEFALSE
382/12/23FALSETRUEFALSE
392/13/23FALSETRUEFALSE
402/14/23FALSEFALSETRUE
412/15/23FALSEFALSETRUE
422/16/23FALSEFALSETRUE
432/17/23FALSEFALSETRUE
442/18/23FALSEFALSETRUE
452/19/23FALSEFALSETRUE
462/20/23FALSEFALSETRUE
472/21/23FALSEFALSETRUE
482/22/23FALSEFALSETRUE
492/23/23FALSEFALSETRUE
502/24/23FALSEFALSETRUE
512/25/23FALSEFALSETRUE
522/26/23FALSEFALSETRUE
532/27/23FALSEFALSETRUE
542/28/23FALSEFALSETRUE
553/1/23FALSEFALSETRUE
563/2/23FALSEFALSETRUE
573/3/23FALSEFALSETRUE
583/4/23FALSEFALSETRUE
593/5/23FALSEFALSETRUE
603/6/23FALSEFALSETRUE
613/7/23FALSEFALSETRUE
623/8/23FALSEFALSETRUE
633/9/23FALSEFALSETRUE
643/10/23FALSEFALSETRUE
653/11/23FALSEFALSETRUE
663/12/23FALSEFALSETRUE
673/13/23FALSEFALSETRUE
683/14/23FALSEFALSETRUE
693/15/23FALSEFALSETRUE
705/18/23FALSEFALSETRUE
715/20/23FALSEFALSETRUE
725/22/23FALSEFALSETRUE
735/24/23FALSEFALSETRUE
745/26/23FALSEFALSETRUE
755/28/23FALSEFALSETRUE
765/30/23FALSEFALSETRUE
776/1/23FALSEFALSETRUE
786/3/23FALSEFALSETRUE
796/5/23FALSEFALSETRUE
806/7/23FALSEFALSETRUE
816/9/23FALSEFALSETRUE
826/11/23FALSEFALSETRUE
836/13/23FALSEFALSETRUE
846/15/23FALSEFALSETRUE
856/17/23FALSEFALSETRUE
866/19/23FALSEFALSETRUE
Sheet1
Cell Formulas
RangeFormula
D2:D86D2=AND(B2<>"",B2<TODAY())
E2:E86E2=B2<DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))
F2:F86F2=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B91Expression=AND(B2<>"",B2<TODAY())textYES
B2:B88Expression=AND(B2<>"",B2<DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())))textYES
B2:B89Expression=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))textNO
 
Last edited:
Upvote 0
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
 
Upvote 0
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
AB
1namedate
2name15/1/22
3name26/1/22
4name37/13/22
5name47/14/22
6name57/15/22
7name68/3/22
8name78/14/22
9name88/15/22
10name99/16/22
11name1010/11/22
12name1110/25/22
13name1211/8/22
14name1311/22/22
15name1412/6/22
16name1512/20/22
17name161/3/23
18name171/14/23
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:C3,B9:C27,B4:B8Expression=AND(B2<=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY())),B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))textYES
B2:C3,B9:C27,B4:B8Expression=AND(B2<>"",B2<=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))textYES
B2:C3,B9:C27,B4:B8Expression=B2>=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))textYES
 
Upvote 0
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
 
Upvote 0
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.

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

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
 
Upvote 0
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
 
Upvote 0
no rush, just would like to gt this right for you ... i get notified when ever you post a reply here
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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