Home

Help with Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-728565%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728565%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3CBR%20%2F%3ECan%20you%20help%20me%20with%20something%20%3F%3CBR%20%2F%3Ei%20want%20to%20compare%202%20calendar%20dates%20in%20excel%20%2C%20for%20example%3A%3CBR%20%2F%3EDATE%201%20-%20DATE%202%3CBR%20%2F%3E10%2F3%2F2017--2%2F6%2F2020%3CBR%20%2F%3E1%2F17%2F2019--1%2F17%2F2020%3CBR%20%2F%3E9%2F25%2F2017--1%2F25%2F2019%3CBR%20%2F%3E3%2F21%2F2018--3%2F21%2F2019%3CBR%20%2F%3Eas%20some%20of%20the%20days%20have%20been%20done%20in%20the%20past%20I%20would%20need%20to%20compare%20the%20dates%20between%20themselves%20and%20between%20%22today%22's%20date%20to%20have%20am%20update%20of%20the%20process%20any%20time%20%3Bas%20this%20dates%20represent%20completion%20days%20of%20our%20colleagues%20training.%3CBR%20%2F%3EDATE%201%20is%20the%20date%20when%20the%20training%20has%20been%20completed%3CBR%20%2F%3EDATE%202%20is%20the%20due%20date%20for%20the%20next%20training%3CBR%20%2F%3EIf%20the%20date%20is%20DATE%202%20%26lt%3B%20TODAY%20by%203%20moths%20range%20it%20will%20be%20highlighted%20with%20red%3CBR%20%2F%3Eif%20the%20date%20is%20DATE%202%26lt%3B%3D%20%2F%3D%26gt%3B%20TODAY%20it%20will%20be%20highlighted%20with%20amber%2Fyellow%3CBR%20%2F%3Eif%20the%20date%20is%20DATE%202%20%26gt%3B%20TODAY%20it%20will%20be%20highlighted%20in%20green%3CBR%20%2F%3Ecan%20you%20guys%20help%20me%20%3A)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-728565%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728998%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728998%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368406%22%20target%3D%22_blank%22%3E%40AlinaDenisaCiobotaru%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20try%20using%20this%20formula%3A%3C%2FP%3E%3CP%3E%3DIF(E2%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3CP%3E(*%20E2%20refers%20to%20a%20date%20in%20the%20DATE%202%20column%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20just%20use%20conditional%20formatting%20of%20using%20a%20formula%20to%20get%20the%20colours%3A%3C%2FP%3E%3CP%3E*%20To%20get%20the%20red%20colour%2C%20use%20the%20formula%3A%26nbsp%3B%3DAND(%24D2%26gt%3BTODAY()%2C%24F2%26lt%3B3)%20where%20F2%20refers%20to%20the%20difference%20between%20TODAY%20and%20DATE%202%20and%20D2%20is%20DATE%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20To%20get%20the%20Amber%2C%20use%20the%20formula%3A%26nbsp%3B%3DOR(%24D2%26gt%3B%3DTODAY()%2C%24D2%26lt%3B%3DTODAY())%3C%2FP%3E%3CP%3E*%20To%20get%20the%20green%2C%20use%20the%20formula%3A%26nbsp%3B%3D%24D2%26gt%3BTODAY()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20arranged%20the%20rules%20in%20this%20order%3A%20Red%2C%20green%20and%20then%20Amber%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20look%20at%20the%20conditional%20formatting%20rules%20within%20the%20workbook%20that%20I%20have%20attatched%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729135%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368406%22%20target%3D%22_blank%22%3E%40AlinaDenisaCiobotaru%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20suggest%20formula%20rules%3C%2FP%3E%0A%3CPRE%3E%3D%24B2%26lt%3B%3DEDATE(TODAY()%2C-3)%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%26lt%3B-%20red%0A%3D(%24B2%26gt%3BEDATE(TODAY()%2C-3))*(%24B2%26lt%3B%3DTODAY())%20%20%20%20%20%20%20%26lt%3B-%20yellow%0A%3D%24B2%26gt%3BTODAY()%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%26lt%3B-%20green%3C%2FPRE%3E%0A%3CP%3Ewhere%20B2%20is%20first%20in%20the%20range%20Date%202%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E
AlinaDenisaCiobotaru
New Contributor

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 Replies

@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

@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.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies