Organizing/Plotting Times? Countif??

Hey Guys; I'm trying to find a way to organize all these times that are in the cells...these are the times a sale was made, so I'm trying to organize this in a way that I can see what our best hours are for sales, and thus for the purpose of scheduling shifts. ie. so I can see that 55 sales were made within the hours of 4-6pm, etc"

You can see the cell has a date and a time within it...Do we need to somehow separate out the time and then do a countif if the cell is within a certain hour? I'm kind of stumped.

I attached the file if someone may know what to do. Thanks you guys;

Put the time 00:00:00 in cell C2 and 01:00:00 in C3, then drag those two down to C25 - you should get all the times every hour up to 23:00:00. Then you can put this formula in D2:

=SUMPRODUCT((MOD(A$2:A$1048,1)>=C2)*(MOD(A$2:A$1048,1)<(C2+1/24)))

then copy this down to D25. That will give you the number of entries for each hour across a consolidated day. Then it is relatively easy to graph that.

Hope this helps.

Pete

Originally Posted by Pete_UK

Put the time 00:00:00 in cell C2 and 01:00:00 in C3, then drag those two down to C25 - you should get all the times every hour up to 23:00:00. Then you can put this formula in D2:

=SUMPRODUCT((MOD(A$2:A$1048,1)>=C2)*(MOD(A$2:A$1048,1)<(C2+1/24)))

then copy this down to D25. That will give you the number of entries for each hour across a consolidated day. Then it is relatively easy to graph that.

Hope this helps.

Pete

You nailed it. Brilliant. Thanks man; you're amazing.

Glad to hear it worked for you - thanks for feeding back.

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