Forum Discussion
Help with Excel
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/...
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