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.

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)