Help with Excel

[AlinaDenisaCiobotaru]

Hi guys,
Can you help me with something ?
i want to compare 2 calendar dates in excel , for example:
DATE 1 - DATE 2
10/3/2017--2/6/2020
1/17/2019--1/17/2020
9/25/2017--1/25/2019
3/21/2018--3/21/2019
as some of the days have been done in the past I would need to compare the dates between themselves and between "today"'s date to have am update of the process any time ;as this dates represent completion days of our colleagues training.
DATE 1 is the date when the training has been completed
DATE 2 is the due date for the next training
If the date is DATE 2 < TODAY by 3 moths range it will be highlighted with red
if the date is DATE 2<= /=> TODAY it will be highlighted with amber/yellow
if the date is DATE 2 > TODAY it will be highlighted in green
can you guys help me :)

[#2]

@AlinaDenisaCiobotaru 

Hey,

Maybe try using this formula:

=IF(E2<TODAY(),MONTH(TODAY()-E2),MONTH(E2-TODAY())) to figure out the difference between DATE 2 and Today

(* E2 refers to a date in the DATE 2 column

Then just use conditional formatting of using a formula to get the colours:

* To get the red colour, use the formula: =AND($D2>TODAY(),$F2<3) where F2 refers to the difference between TODAY and DATE 2 and D2 is DATE 2

* To get the Amber, use the formula: =OR($D2>=TODAY(),$D2<=TODAY())

* To get the green, use the formula: =$D2>TODAY()

I've also arranged the rules in this order: Red, green and then Amber

Have a look at the conditional formatting rules within the workbook that I have attatched

[#3]

@AlinaDenisaCiobotaru 

I'd suggest formula rules

=$B2<=EDATE(TODAY(),-3)                       <- red
=($B2>EDATE(TODAY(),-3))*($B2<=TODAY())       <- yellow
=$B2>TODAY()                                  <- green

where B2 is first in the range Date 2 cell.