cdb424ttm
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
Check my example...hope is what you want.
ASKER
Thank you both, it helps a lot.
ASKER
Can you explain to me how it works? Thanks
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$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
=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
ASKER
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
One problem I see it that it is not counting the time between 11:30 and 12:pm for some reason
sample.xls
ASKER
Sorry that would be 11:30pm to 12:00 am.
ASKER