# Count number of cells with specific date and time range

1. ## Count number of cells with specific date and time range

I have a spreadsheet with raw data, D column has dates, E column has times.

D2 - 9/25/2013
D3 - 9/25/2013
D4 - 9/24/2013
D5 - 9/23/2013

E2 - 21:53
E3 - 21:48
E4 - 8:12
E5 - 10:46

I would like to have a formula to put in column F that would count how many cells have 9/25/2013 in D as a date and time in E column that has a time between 21:00 - 21:59.

Would this be possible?

Thanks,

2. ## Re: Count number of cells with specific date and time range

Use

=COUNTIFS(D:D,DATE(2013,9,25),E:E,">="&TIME(21,0,0),E:E,"<="&TIME(21,59,0))

3. ## Re: Count number of cells with specific date and time range

=SUMPRODUCT(IF(D2:D5="9/25/2013",1,0)*IF(E2:E5>=0.875,1,0)*IF(E2:E5<=0.91667,1,0))

it's an array formula so needs to be confirmed with ctrl+shift+enter.

Edit: or just use Ace's formula.

4. ## Re: Count number of cells with specific date and time range

Thanks for the answers, I've tried both of these and they are both returning a 0. When I know they should have a count. Any suggestions?

5. ## Re: Count number of cells with specific date and time range

Make sure the dates are actually dates and not text. Ditto with the time.

Do this
- In a separate cell input 1
- Copy this cell
- Paste Special -- Multiply -- values
- Ok

6. ## Re: Count number of cells with specific date and time range

Try this:

Copy and paste formula below

=COUNTIFS(\$D\$2:\$D\$5,"9/25/2013",\$E\$2:\$E\$5,">21:00",\$E\$2:\$E\$5,"<21:59")

7. ## Re: Count number of cells with specific date and time range

Sorry guys I still cannot get it, I'm probably over my head here anyways. I've attached the file, the columns are changed but I believe it still should work. Could someone take a look for me?

Chat Data-2.xlsx

8. ## Re: Count number of cells with specific date and time range

Paste in C2:

=COUNTIFS(A:A,"9/23/2013",B:B,">21:00",B:B,"<21:59")

9. ## Re: Count number of cells with specific date and time range

Your dates appear to be stored in column A as Text Strings, not real dates.

Try this
Copy a blank cell
Highlight column A
Right click - Paste Special - Values - Add
OK

10. ## Re: Count number of cells with specific date and time range

Thank you for all your help everyone, I ended up putting a 0 in front of the date 9/23/2013 in the formula and it works! Thanks again all!

11. ## Re: Count number of cells with specific date and time range

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1