Date comparison not working in Excel formula

excel-formula
Tom Sharpe picture Tom Sharpe · Oct 9, 2014 · Viewed 87.5k times · Source

If I put the date 31/12/2013 in A1 and another date 1/1/2014 in A2 then a formula like

 =A1<A2

gives the expected result, TRUE.

If I put the formula 

=A1<1/1/2014

in another cell, it gives the result FALSE.

The question is how to adjust the second formula to make it give the correct result, and why it doesn't work as it stands.

I've been looking at this for a while and have found some related posts like

Comparing computed dates with entered dates

but not one which directly answers the question.

Answer

Alex K. picture Alex K. · Oct 9, 2014

1/1/2014 is 1 divided by 1 divided by 2014.

Instead; =A1<DATEVALUE("1/1/2014")

Related questions

Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More