## Count entries between date and time criteria

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

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

The image above demonstrates the AND function with two logical expressions. If the value in column B is equal to […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Highlight specific time ranges in a weekly schedule

In a previous post I created a simple weekly schedule with dynamic dates. In this post I am going to […]

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each […]

### 3 Responses to “Count entries between date and time criteria”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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