Home

Date Comparison

%3CLINGO-SUB%20id%3D%22lingo-sub-674816%22%20slang%3D%22en-US%22%3EDate%20Comparison%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674816%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20two%20compare%20dates%20in%20the%20form%26nbsp%3B6%2F2%2F2019%20(MDY)%20and%205%2F15%2F2018.%20I%20want%20it%20to%20tell%20me%20if%20one%20is%20more%20recent%20than%20the%20other.%20I%20am%20trying%20to%20compare%20them%20using%20the%20'%26gt%3B'%20and%20%22%26lt%3B%22%20operators%2C%20but%20it%20is%20not%20working.%3C%2FP%3E%3CP%3EIf%20you%20have%20any%20experience%20and%20insight%20on%20this%20problem%2C%20please%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-674816%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-674866%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Comparison%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-674866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355864%22%20target%3D%22_blank%22%3E%40Benhill%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DIF(AND(ISNUMBER(A2)%2CISNUMBER(B2))%2CIF(A2%26gt%3BB2%2C%22Date%201%20is%20more%20recent%20than%20Date%202.%22%2CIF(A2%26lt%3BB2%2C%22Date%202%20is%20more%20recent%20than%20Date%201.%22%2C%22The%20two%20dates%20are%20equal!%22))%2C%22%22)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117348iC7C0FD2588E22EFE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Comparison%20Between%20Two%20Dates.png%22%20title%3D%22Comparison%20Between%20Two%20Dates.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%2C%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675904%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Comparison%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675904%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355864%22%20target%3D%22_blank%22%3E%40Benhill%3C%2FA%3E%26nbsp%3Bhello%20there.%20Could%20you%20be%20more%20specific%20about%20what%20doesn't%20work%20exactly%3F%20Since%20dates%20are%20essentially%20numbers%20(i.e.%20serial%20numbers)%2C%20a%20comparator%20can%20be%20used%20without%20issue.%20The%20formula%20provided%20above%20ensures%20both%20dates%20entered%20are%20actually%20numbers%20and%20not%20text%2C%20which%20could%20very%20well%20be%20your%20problem.%20An%20easy%20way%20to%20check%20is%20to%20remove%20any%20horizontal%20alignment%20and%20widen%20the%20column%3B%20if%20the%20data%20is%20left-aligned%20it's%20seen%20as%20text%2C%20whereas%20if%20it's%20right-aligned%20it's%20seen%20as%20numerical%20(including%20dates).%20If%20this%20is%20the%20case%2C%20either%20coerce%20the%20textual%20date%20to%20be%20numerical%2C%20or%20coerce%20your%20formula%20dependencies.%20Generally%20it's%20better%20to%20do%20the%20former.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675981%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Comparison%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F19137%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20replies%2C%20I%20got%20the%20problem%20figured%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Benhill
New Contributor

Hello,

I am trying two compare dates in the form 6/2/2019 (MDY) and 5/15/2018. I want it to tell me if one is more recent than the other. I am trying to compare them using the '>' and "<" operators, but it is not working.

If you have any experience and insight on this problem, please let me know.

 

Thanks.

3 Replies
Highlighted

@Benhill

 

Please try this formula:

=IF(AND(ISNUMBER(A2),ISNUMBER(B2)),IF(A2>B2,"Date 1 is more recent than Date 2.",IF(A2<B2,"Date 2 is more recent than Date 1.","The two dates are equal!")),"")

Comparison Between Two Dates.png 

 

Best Regards,

Haytham

@Benhill hello there. Could you be more specific about what doesn't work exactly? Since dates are essentially numbers (i.e. serial numbers), a comparator can be used without issue. The formula provided above ensures both dates entered are actually numbers and not text, which could very well be your problem. An easy way to check is to remove any horizontal alignment and widen the column; if the data is left-aligned it's seen as text, whereas if it's right-aligned it's seen as numerical (including dates). If this is the case, either coerce the textual date to be numerical, or coerce your formula dependencies. Generally it's better to do the former.

@Zack Barresse 

@Haytham Amairah 

 

 

Thank you for your replies, I got the problem figured out.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies