title: Average day and night temperature in excel
body: <p>I have a large dataset of temperature and humidity readings, collected every half hour for a year. I'd like to get an average of day and night temperature, but my experiments with averageifs have so far failed. This is as far as I got.</p>
  
  <pre><code>=AVERAGEIFS(Sheet1!D$7:D$17622,Sheet1!$C$7:$C$17622, "&gt;="&amp;$A21, Sheet1!$C$7:$C$17622,"&lt;" &amp;$A22)
  </code></pre>
  
  <p>The values in A21 and A22 are 6:00 and 18:00, formatted as h:mm.</p>
  
  <p>This just returns a DIV/0! error - I don't have any zeros, so I don't know what the issue is. </p>
  
  <p>I have exactly the same formula working with dates. Both formulas are drawing on the same input, entered into each cell like so:</p>
  
  <pre><code>1/02/2016  3:00:00 PM
  </code></pre>
  
  <p>I would be most grateful for any assistance (or if this is the wrong forum, let me know).</p>