title: How does the logical test in an Excel if statement evaluate dates in different formats?
body: <p>Spreadsheet with example data is linked <a href="https://www.dropbox.com/s/bre1uha6lta3kan/Dates.xlsx?dl=0" rel="nofollow">here</a>.</p>
  
  <p>Suppose you have 3 dates "4/1/2015", "6/30/2015", and "5/1/2016" set to the Date cell format in Excel.</p>
  
  <p>You can directly compare the dates using logical operators (&lt;, >, etc.). If "4/1/2015" was in Cell A1 and "6/30/2015" was in Cell A2, this formula <code>=IF(A1&gt;A2,"True","False")</code> evaluates to "False".</p>
  
  <p>However, you cannot compare a date against a string literal (i.e. "3/31/2015"). If "4/1/2015" was in Cell A1, this formula <code>=IF(AND(A1&gt;="4/01/2015",A1&lt;="6/30/2015"),"True","False")</code> always evaluates to "False".</p>
  
  <p>With the above noted, why is it that by appending an arithmetic operation to a string literal; Excel allows the comparison between dates stored in different formats? For example, if "4/1/2015" was in Cell A1, this formula <code>=IF(AND(A1&gt;="4/01/2015"+0,A1&lt;="6/30/2015"+0),"True","False")</code> will evaluate to "True".</p>