We help IT Professionals succeed at work.

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

on
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
Comment
Watch Question

## View Solutions Only

Most Valuable Expert 2013
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

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.
Top Expert 2011
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
Top Expert 2011

Commented:
@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.

Commented:
Thank you both, it helps a lot.

Commented:
Can you explain to me how it works?  Thanks

Most Valuable Expert 2013

Commented:
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

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

Commented:
Sorry that would be 11:30pm to 12:00 am.