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

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.

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

Pete, it worked for cells G2 and G3, but after that it returned a 0 in each cell. There is data for these dates, but for some reason the formula only picked up the first 2 in the column.
I have attached the worksheet. It has about 4100 rows.
Any thoughts?
Thanks
Scott

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

Please Login or Register  to view this content.

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

Originally Posted by scott3294

... but for some reason the formula only picked up the first 2 in the column...

That's because the formula is only looking at the ranges down to row 400. Change 400 to 4400 (3 times) in the formula in G2, then copy it down again.

Hope this helps.

Pete

Thanks Pete!!
Again, this group is fantastic.
Thanks for all the help.
Scott

You're welcome, Scott - glad you got it working.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

This seems simpler (F2 is the date, 12/2/2013):

Please Login or Register  to view this content.

The OP's version is XL2003 in his profile, and COUNTIFS was not available in that version (I know he subsequently attached an .xlsx file).

Pete