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

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

=SUMPRODUCT((MOD(A1:A10,1)

regards, barry

=SUMPRODUCT((MOD(B5:B1281;

See attached sampe (added the time itself in a column to double check)

sample.xls

Check my example...hope is what you want.

=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$128

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

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial