title: Excel: Counting how many rows fall within a time period
body: <p>I have an excel sheet with 2 columns</p>
  
  <p>Column A = "CA", "CR" or "IN"</p>
  
  <p>Column B = Date &amp; time format DD/MM/YYYY HH:MM</p>
  
  <p>I want to make a count at the bottom of a column for each row that has this criteria:</p>
  
  <p>i) Row 1-8 = "CA"</p>
  
  <p>ii) Row 1-8 needs to check for a time range, namely > "17:00" and &lt; "04:59"</p>
  
  <p>This is what I've come up with so far:</p>
  
  <p>=COUNTIFS(A2:A8,"CA",B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)>"04:59"), B2:B8,RIGHT(TEXT(B2:B8,"hh:mm"),5)&lt;"17:00")</p>
  
  <p>I presume using a range within a the text function is wrong, but don't know how to resolve this.</p>
  
  <p>Because Column B is in a date and time format, I'm having to change it to a string within the function so I can make a test on just the time - Maybe there's a better way?)</p>
  
  <p>Thanks</p>