Formula/ Conditional Formatting Help please [Closed]

Report
Posts
3
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
December 19, 2017
-
Posts
1843
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 29, 2020
-
Hello,

I am trying to create a tracker spread sheet. I need to enter the date a course was completed, the date it is due (2 years later) and I need it to highlight in red when it is 6 months before the due date in order to allow time to arrange the next course. Can anyone please help. The course was completed on 05/12/2017 and will be due on the 05/12/2019. so I need it to go red 6 months before 05/12/19.
Any help or advice would be greatly appreciated. Thanks in advance.

2 replies

Posts
1843
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 29, 2020
136
Amygreen85, Good morning.

Suppose:

D3 --> DUE DATE --> 05/12/2019

CONDITIONAL FORMATTING
Select D3
Menu --> Format --> Conditional Cormatiing
1st Rule
The formula is: =TODAY()>EDATE(D3;-6)
Format: Background colour RED
OK

Please, tell us if it worked.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Posts
3
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
December 19, 2017

Thank you so much for your reply. Its telling me that there is an error in the formula when I enter it in, I changed D3 to E3 as that is the column the due date is in my spread sheet. Any ideas would be very much appreciated.

Thank you
Posts
1843
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 29, 2020
136
Amygreen85, Good morning.

Since you did not say what the mistake is, it gets a bit more difficult to help.

But maybe it could be the separator inside the EDATE function.
It may be that your version uses a comma (,) instead of a semicolon (;) in separating the operators from a function.

Try to use:
=TODAY() > EDATE(D3,-6)

instead of: =TODAY()>EDATE(D3;-6)

Please, tell us if it worked.
I hope it helped.
--
Belo Horizonte, Brasil.
Marcílio Lobão