title: DATEDIF on a Range in Excel
body: <p>I have an Excel table with 2 columns for Created Date and Closed Date. I'm trying to get a count of records within a certain range of lifetime values (lifetime = # days between Created and Closed Date).  So for example, how many records have a lifetime of 40+ days.</p>
  
  <p>I can't modify the table to add new columns (I'm trying to do my calculation off in a separate sheet without modifying the main sheet with the data).</p>
  
  <p>What I tried to do is this:</p>
  
  <pre><code>=COUNTIF(DATEDIF(MyTable[CreatedDate], MyTable[ClosedDate], "d"), "&gt; 40")
  </code></pre>
  
  <p>That formula doesn't work, I believe for a couple of reasons: You can't pass ranges to DATEDIF, also DATEDIF returns a discrete value but COUNTIF expects a range.</p>
  
  <p>It appears that what I'm trying to do obviously doesn't work on Ranges. Is there a way to do a DATEDIF between two Ranges, that then returns a new Range so that I can COUNTIF it?  Or some other way to accomplish my goal?</p>
  
  <p>Some Sample Data (Lifetime is not an actual column in my table, just included here for convenience):</p>
  
  <pre><code>Created Date      Closed Date      Lifetime
  9/20/2012         10/19/2012       29
  9/20/2012         10/27/2012       37
  9/20/2012         12/26/2012       97
  9/20/2012         10/23/2012       33
  9/20/2012         11/23/2012       64
  10/19/2012        12/14/2012       56
  </code></pre>
  
  <p>Note: Every record will have a valid Created and Closed Date.</p>
  
  <p>UPDATE: @pnuts suggested a formula that may or may not work:</p>
  
  <pre><code>=COUNTIF(MyTable[ClosedDate], "&gt;"&amp;MyTable[CreatedDate]+40)
  </code></pre>
  
  <p>I'm not sure exactly what's going on, but depending on what cells in my worksheet I put that formula in I get different results.  Here's a screenshot, every cell has an identical formula as shown in the formula bar.</p>
  
  <p><img src="https://i.stack.imgur.com/eWErE.png" alt="enter image description here"></p>
  
  <p>If anybody wants to try it out in my sample Excel file, you can grab it here: <a href="https://dl.dropboxusercontent.com/u/20949564/Lifetime%20Sample.xlsx" rel="nofollow noreferrer">Sample Excel File</a></p>