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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

barry houdiniCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
xtermieCommented:
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
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.