Looking for a formula that counts between two times

Hi All,
You guys were such great help on my last issue, that I am coming back for more. :-) I researched how I might do this, but keep getting stumped by part of it.
I have data over the last 170 days. Each entry has a time stamp. I would like to count how many entries there are between 7:45 am and 2:45pm. A total count across all 170 days is needed, but it would be great if I could break that down by calendar day (i do have that as a column).
Here is a sample of the data:Friday 11/15/13 07:08AM
Friday 11/15/13 07:08AM
Friday 11/15/13 07:16AM
Friday 11/15/13 07:16AM
Friday 11/15/13 07:46AM
Friday 11/15/13 07:46AM

And the sheet has dates starting in June and ending in mid December.

Thanks for suggestions.
Scott

Suppose your example data is in column A and is actual Excel-dates formatted to look like that, and your list of calendar dates is in column D, starting in D2, then you can put this formula in E2:

=SUMPRODUCT((INT(A$1:A$1000)=D2)*(MOD(A$1:A$1000,1)>=--"7:45:00")*(MOD(A$1:A$1000,1)<=--"14:45:00"))

Adjust the cell references to suit, then copy down.

Hope this helps.

Pete

Originally Posted by Pete_UK

Suppose your example data is in column A and is actual Excel-dates formatted to look like that, and your list of calendar dates is in column D, starting in D2, then you can put this formula in E2:

=SUMPRODUCT((INT(A$1:A$1000)=D2)*(MOD(A$1:A$1000,1)>=--"7:45:00")*(MOD(A$1:A$1000,1)<=--"14:45:00"))

Adjust the cell references to suit, then copy down.

Hope this helps.

Pete

Each date element is in its own cell.
Time is one column, date is one column, and day is one column.
Does that help??
Thanks
Scott

Attach a sample workbook.

Pete

Here is a sample.
Thanks
Scott

Your "times" in column C are text values - to convert them to proper times you can put this formula in D2:

=(LEFT(C2,5)&":00")+IF(RIGHT(C2,2)="PM",0.5,0)

Format that cell as TIME, then copy the formula down.

You can derive the dates in column F using this formula in F2:

=MIN(B:B)

and this one in F3:

=F2+1

Format both cells as dates, then copy the formula in F3 down as far as you need. Then in G2 you can have this formula:

=SUMPRODUCT((B$2:B$400=F2)*(D$2:D$400>=--"7:45:00")*(D$2:D$400<=--"14:45:00"))

(which results in 90 in your sample file), then copy this down for as many dates as you have in column F.

Hope this helps.

Pete

The other option is to do a pivot table out of the data. Filtering timestamps between certain hours could be a bit tricky, but the result will be neatly formatted and include all data just in one step (without creating manually list of dates). Anyway, again sample data in a spreadsheet would make answering easier/more precise.

other way to have real time from "time like text":

=TIMEVALUE(LEFT(C2,5)&" "&RIGHT(C2,2))

Check out in attached file, how (with 2 IFs) it could be used in Pivot Table.