Excel cell with date and time need to just get time?

My work sheet has a row of cell that have Like 11/11/2011  12:12:00 PM.
What I'm trying to do is use a sumproduct() to pull the count all the cells that have from
12:00:00 AM to 12:30:00 AM , 12:31:00 AM to 1:00:00 AM and so on. I can't get it to work, or I don't know how to set it up. Can some one help me do this?
Hours.xls
cdb424ttmAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
You can get a time from a date and time using MOD...so for range A1:A10 try

=SUMPRODUCT((MOD(A1:A10,1)>="12:00"+0)*(MOD(A1:A10,1)<="12:30"+0))

regards, barry
0
 
cdb424ttmAuthor Commented:
I tried it but it is only giving me true, I'm looking for it to give me a count of the items that are between those times. I hope I explaining it right.
0
 
xtermieConnect With a Mentor Commented:
Building on what barry says...you should be able to make it varibale by using your whole range and references to the time frames to the cells:
=SUMPRODUCT((MOD(B5:B1281;1)>=E5+0)*(MOD(B5:B1281;1)<=E6+0))

See attached sampe (added the time itself in a column to double check)
sample.xls
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
xtermieCommented:
@cdb424 it was giving true because you did not substitute barry's sample references with actual cell references from your data (range a1:a10 is blank your data).
Check my example...hope is what you want.
0
 
cdb424ttmAuthor Commented:
Thank you both, it helps a lot.
0
 
cdb424ttmAuthor Commented:
Can you explain to me how it works?  Thanks

0
 
barry houdiniCommented:
Mod simply gives the remainder when you divide by the specified divisor, so if you use

=MOD(20,7)

that returns 6 because when you divide 20 by 7 the remainder is 6.

For time/date values in Excel a day is 1 and therefore the decimal part of any number is the time, e.g. 0.5 is noon, 0.75 is 6 PM etc. so to get the time from a date/time value you can use MOD with the divisor set to 1....the remainder is the decimal part of the number = the time

Note: to ensure that you don't "double count" for your example you can change the formula slightly to this:

=SUMPRODUCT((MOD(B$5:B$1281,1)>=E5)*(MOD(B$5:B$1281,1)<E6))

Note that the <= has been changed to < to avoid double counting values on the half hour. I put that formula in F5 and copied down and the total of column F came to 1277...which is exactly the number of values in column B....

regards, barry
0
 
cdb424ttmAuthor Commented:
Thanks Barry,
One problem I see it that it is not counting the time between 11:30 and 12:pm for some reason
sample.xls
0
 
cdb424ttmAuthor Commented:
Sorry that would be 11:30pm to 12:00 am.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.