title: DateDiff function unexpectedly returning another date or a default
body: <p>I'm trying to use the DateDiff function for a macro in excel using VBA.</p>
  
  <p>To my understanding the code is supposed to return an integer value.</p>
  
  <p>Here are the examples given online at this location: <a href="http://www.techonthenet.com/access/functions/date/datediff.php" rel="nofollow">http://www.techonthenet.com/access/functions/date/datediff.php</a></p>
  
  <p>-DateDiff ("yyyy", #15/10/1998#, #22/11/2003#)  would return 5</p>
  
  <p>-DateDiff ("m", #15/10/2003#, #22/11/2003#) would return 1</p>
  
  <p>-DateDiff ("d", #15/10/2003#, #22/11/2003#) would return 38</p>
  
  <p>Here is the code that I used:</p>
  
  <pre><code>Sub macrotest()
  
  Dim dateNow, dateThen, dateFinal As Date
  
  dateNow = Format(Sheet1.Cells(2, 2), "DD/MM/YY")
  dateThen = Format(Sheet1.Cells(3, 2), "DD/MM/YY")
  dateFinal = DateDiff("d", dateNow, dateThen)
  
  Sheet1.Cells(5, 2) = dateFinal
  
  
  End Sub
  </code></pre>
  
  <p>Lastly, This is what I had in my excel rows:</p>
  
  <p>B2:  5/30/12</p>
  
  <p>B3:  3/30/12</p>
  
  <p>B5:  12:00:00 AM    (this cell was supposed to have the result listed).</p>
  
  <p>-I got either dates that excel defaults to, or 12:00.</p>
  
  <p>Any Ideas? Thanks!</p>