How to find the number of rows that have date time fields between 5 and 6 PM

[jshwhitlow]

I am using Microsoft Excel 2011 for Mac.

I have a phone log that I want to report some data off of. All of the solutions out there that I have looked up seem to assume I only have a time value and that they don't include an actual date.
I have a bunch of dates like this:

• 2/1/16 3:39 AM
• 2/1/16 10:06 AM
• 2/1/16 10:27 AM
• 2/1/16 10:34 AM
• 2/1/16 10:38 AM

Except I have around 1200 phone calls. (1288 rows total)
I want to know how many phone calls we have received between 5PM and 6PM.
So far I have tried the following and none of them worked:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">=COUNTIFS(C2:C1288,">="&B1,C2:C1288,"<="&B2)

=COUNTIF(C2:C1288,">=17:00:00")-COUNTIF(C2:C1288,">18:00:00")

=SUMPRODUCT((C2:C1288>=TIME(17,0,0))*(C2:C1288 <=TIME(18,0,0)))
</code>Bonus points if you can also help me group phone numbers to tell me how many rows contain the same numbers. (5555555555 has 3 rows)(4444444444 has 2 rows)

• 5555555555
• 4444444444
• 3333333333
• 6666666666
• 5555555555
• 8888888888
• 5555555555
• 4444444444

 

[DaveBlakeMAAT]

I would probably add a helper column to extract the time out of the existing cell.

= ROUND(RIGHT(TimeDateCell,LEN(TimeDateCell)-FIND(".",TimeDateCell,1)+1),4)

From their you should be able to achieve what you want, either via a pivot table or a countif formula.

note I am assuming your date/time field is stored as a serial number.

 

[maabadi]

Hi jshwhitlow
For Problem 1: Make sure you have change format of columns to time. Time in cell writed basedon 24 hours. For Example 18:00:00 equal to 18/24= 0.75
Thus for your problem and count no. of phone calls:

=COUNTIFS(B2:B1200,"<= 0.75",B2:B1200,">= 0.708333333")

For Problem 2:

• Click the Data Ribbon Menu
• Select the Advanced Button in the Sort & Filter section
• Fill in the dialog Box, copying the results to another location and you have same range for List range and Criteria Range ($B2:$B1200) and for Copy to select first cell of another Column (F2) and making sure you tick Unique records only

And You have List of number on another column (example Column F From F2:F200)
After that you write this formula to neighbor column ( Column G) Cell G2.

=COUNTIF($B$2:$B$1200,F2)

And fill down to last listed rows cell (G200)

 

[AlanY]

try this

=SUMPRODUCT(--(TIME(HOUR(C2:C1288),MINUTE(C2:C1288), SECOND(C2:C1288))>=TIME(17,0,0))*--(TIME(HOUR(C2:C1288),MINUTE(C2:C1288), SECOND(C2:C1288))<=TIME(18,0,0)))

 

[AlanY]

for the phone numbers in column A, try this in column B and copy down

=COUNTIF(A:A,A1)