formula help for dates that are in date

[magimagpie]

Any help will be appreciated if you can please.

I have a column of dates, they are yearly courses that people have to undertake. If they don't refresh within the year they are out of date.


Anything out of date goes red using this formula
=NOW()

Anything due to time out within the next 60 days goes amber using this formula
=NOW()+60

I just need something to make them stay green for the rest of the time until they turn amber...

 

[Joe4]

Do you mean between those two things?
Let's say that the value is in A1. Then you would use a Conditional Formatting formula like:

=AND(A1 > NOW(),A1 < (NOW()+60))

If that is not what you are after, please provide some different data sample where you show us different values with the different colors that you want.

 

[magimagpie]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]A2
[/TD]
[/TR]
[TR]
[TD]Staff name
[/TD]
[TD]Course expiry date
[/TD]
[/TR]
[TR]
[TD]Sarah Smith
[/TD]
[TD]04/11/2019
[/TD]
[/TR]
[TR]
[TD]Sarah Jones
[/TD]
[TD]07/09/2017
[/TD]
[/TR]
[TR]
[TD]John Smith
[/TD]
[TD]06/06/2018
[/TD]
[/TR]
[TR]
[TD]John Jones
[/TD]
[TD]10/11/2018
[/TD]
[/TR]
</tbody>[/TABLE]

Here is a basic sample of some of the data I am looking at.

The course expiry date column is where I have green, amber, red. The one's that I would want green are 04/11/2019, the ones that would be red are 07/09/2017 and 06/06/2018. The one that would be amber is 10/11/2018. I've cracked the red and amber but just not the green...

I've used conditional formatting for this column to get dates to go amber and red but can't work out the green...

 

[Joe4]

Are your dates in a mm/dd/yyyy format, or a dd/mm/yyyy format (it is impossible to determine from the sample data given)?

Also, your logic is not quite clear. I believe that is probably because the formula in your original post got truncated, specifically this one here:

Anything out of date goes red using this formula
=NOW()

I suspect that this is the cause of that: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

Can you re-post that formula, surrounding and > or < signs with spaces, and/or explain the logic?

 

[magimagpie]

I got the formula from a lot of googling so suspect it will be my fault that it's wrong. I'm still an excel newbie so any help on what formulas to use would be appreciated. With regards to the date format, I'm using dd/mm/yyy

 

[Joe4]

Can you please do what I asked for in my last reply?
It is hard for us to see what you are doing because it appears that the formula in your original post did not come through correctly (see the comments in my last post).
So can you please try re-posting that formula, noting the tips I provided on how to get it to appear correctly?

Alternatively, you can just explain (in plain English), the three conditions for the three different colors.

 

[magimagpie]

The formula =NOW() is what I've been using, it was what I'd found whilst googling. It hasn't truncated, sorry I didn't make this clear.

If I complete a training course today 6th November 2018, I will need to re-take it again by 5th November 2019 to remain in date for my training. Once I have completed the course in 2018, the spreadsheet will be updated with the expiry date 05/11/2019.

I want the cell with the expiry date to stay green using conditional formatting until 5th October 2019. From 6th October 2019 until 5th November 2019 I would like the cell colour to be amber using conditional formatting. I then would like the cell to go red on 6th November 2019 as this would show that the training for said person has expired and they are no longer date.

Does this make sense?

 

[Joe4]

The formula =NOW() is what I've been using, it was what I'd found whilst googling. It hasn't truncated, sorry I didn't make this clear.

Since NOW() has not only a date component, but also a time component, the entry will only ever go red for a brief second time, in which the value equals the date and time of that value. So that formula will not do what you want.

I recommend using the Formula option for your Conditional Formatting. Let's say the expiry date is in cell A2. Then I think maybe this is what you want:
Green: =A2<=TODAY()
Amber: =AND(A2>TODAY(),A2<=(TODAY()+30))
Red: =A2>(TODAY()+30)