## Count entries between date and time criteria

*Article updated on January 22, 2018*

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**

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

*Example 2*

**Formula in cell E6:**

**Conditional formatting formula:**

**Download excel 2007 file *.xlsx
**toolman.xlsx

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 3 Responses to “Count entries between date and time criteria”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

How can I format a negative time format -00:00 and then show it in a Chart from -04:00 to 04:00?

Awesome... Thanks! I've been looking for this example for days now..

If you wanted to add a further count into C for example. So you are counting between a date, a certain criteria and then the amount how would you go about this? using example 1 above if ITEM A was sometimes a square and sometimes a circle. How would you count this extra criteria?

Thank.s