Formula/macro help with Dates?

[Dpcombs]

Good afternoon guys/girls!

I am am new to this board and pretty much to excel so I reach out to all you experts for some very much needed help as I need this for my job role.

I am recording training for staff at work and require a traffic light system for training dates, I require the following cells to change colour

. When training is in date the cell automatically goes green.
. When training is coming up to the last month before expiry the cell goes orange/amber.
. When the training date expires the cell automatically goes red.

could somebody please help or guide me how to do this please?

 

[Fluff]

Re: Formula/macro help with Dates?e

Hi & welcome to MrExcel.
Something like

Excel 2013/2016
	A	B
1	Name	Owner
2	Tom Pearce	01/01/2019
3	Bill Brewer	23/04/2019
4	Jan Stewer	10/05/2019
5	Peter Gurney	05/06/2019
6	Peter Davy	10/06/2019
7	Dan'l Whiddon	23/12/2019
8	Harry Hawke	24/01/2020
Database

Using conditional formatting with the following Formulae
Red: =B2<TODAY()
Amber: =B2<=EDATE(TODAY(),1)
Green: =B2>=TODAY()

with Applies to range as B2:B8

 

[Dpcombs]

Re: Formula/macro help with Dates?e

Hi, firstly thank you for taking the time out to reply to my thread!
yes the is exactly what I need so basically 01/01/2019 training is in date and training runs out on 01/06/2019

.01/01/2019 cell will automatically change to green
.01/05/2019 cell will automatically change to amber as its in month before expiry
.01/06/2019 cell will automatically change to red as it as now expired

 

[Fluff]

Re: Formula/macro help with Dates?e

Select the cells you want it to work on (in my example B2:B8), then on the Home tab select conditional formatting > New rule > Use a formula
and for red use =B2< TODAY()
for amber use =B2<=EDATE(TODAY(),1)
& green =B2>=TODAY()

 

[Dpcombs]

Re: Formula/macro help with Dates?e

Select the cells you want it to work on (in my example B2:B8), then on the Home tab select conditional formatting > New rule > Use a formula
and for red use =B2< TODAY()
for amber use =B2<=EDATE(TODAY(),1)
& green =B2>=TODAY()

Thank you for the help, I will give these a try and hopefully carry out my first task successfully

Kind regards, Damian

 

[Fluff]

Re: Formula/macro help with Dates?e

You're welcome & thanks for the feedback

 

[Dpcombs]

Re: Formula/macro help with Dates?e

Good morning,

Hope you are well!

I am really struggling with this (sorry) as I’m only just learning this side of excel as it as mainly always been done for me.

would it be possible if you could take the time to guide me through it (baby steps), it would be very much appreciated

regards, Damian

 

[Fluff]

Re: Formula/macro help with Dates?e

What is the range you want this to work on?

 

[Dpcombs]

Re: Formula/macro help with Dates?e

in terms of what my friend? I'm probably coming across as very stupid but only just learning how excel works as a beginner especially trying to manipulate data! I just want the cells to automatically change colour ie when the current training is in date it stays green then a month before expiry it turns amber and then once the training expiry date arrives it automically turns red which hopefully by turning amber will then remind me to re train my staff on lets say manual handling course. these courses have a 12 month date on them.

 

[Fluff]

Re: Formula/macro help with Dates?e

Which cells do you want to change colour?