Question:

My issue is that I get the date in this format:

7/23/2011 7:00:00 AM

I am trying to count how many entries are between date and time. So I have a shift that starts at 6:30:00 PM and leaves at 7:00:00 AM the next morning. I have tried to convert that to a value to no avail. I have the cells formatted correctly and tried your other formula =SUM(IF(($A$2:$A$10$D$1),1,0)) + CTRL + SHIFT + ENTER but that returned all records. I am starting to think that Excel (I'm using 2010) cannot differentiate between the date and time. Any ideas would be greatly appreciated.

Answer:

Example 1

Excel 2007 formula in cell E2:

=COUNTIFS(A2:A16, "<="&E2, A2:A16, ">="&E1, B2:B16, E3)

Excel 2003 formula in cell E2:

=SUMPRODUCT((A2:A16<=E2)*(A2:A16>=E1)*(B2:B16=E3))

Highlight entries using conditional formatting

  1. Select cell range A2:B16
  2. Click Home tab on the ribbon
  3. Click "Conditional Formatting" button the ribbon.
  4.  Click "New Rule..."
  5. Click "Use a formula to determine which cells to format:"
  6. Click "Format values where this formula is true:"
  7. Type:
    =AND($A2>=$E$1, $A2<=$E$2, $B2=$E$3)

  8. Click "Format.." button
  9. Click Fill tab
  10. Select a color.
  11. Click OK.
  12. Click OK.
  13. Click OK.
Example 2

Formula in cell E6:

=SUMPRODUCT((((A2:A16-INT(A2:A16))<=E2)+((A2:A16-INT(A2:A16))>=E1))*(B2:B16=E3))

Conditional formatting formula:

=(($E$2>=($A2-INT($A2)))+($E$1<=($A2-INT($A2))))*($B2=$E$3)

Download excel 2007 file *.xlsx
toolman.xlsx