Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with date and time?
My data is:
Date Time Calls Offered 8/21/2007 10:00:00AM 2 8/21/2007 11:00:00AM 4 8/21/2007 11:30:00AM 2 8/21/2007 12:30:00PM 1 8/21/2007 1:00:00PM 2 8/21/2007 3:30:00PM 1 8/21/2007 4:00:00PM 1 etc. Note that the data is down to the 30min, but not all times are listed. Since I have to use the data across multiple weeks, I want to use sumproduct as a generic formula to grab numbers and put them into a pivotable table. My receiving tab is as such: A B C D Time 8/21/2007 8/22/2007 8/23/2007 etc 12:00:00AM 12:30:00AM 1:00:00AM 1:30:00AM 2:00:00AM 2:30:00AM 3:00:00AM 3:30:00AM 4:00:00AM etc. to 11:30pm I tried using: =SUMPRODUCT(--((Data!$B$2:$B$65500)=$B2),--((Data!$A$2:$A$65500)=$A2),(Data!$C$2:$C$65500)) Where the "Data" tab has dates in B2:B65500 and times in A2:A65500 and calls offered numbers in C2:C65500. When I use this formula I get zeroes even in cells that clearly have a number in there. I've tried different formats but still I get zero. Am I doing something wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with date and time? | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) |