Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
I am looking to solve the following problem Col A Col B Col C Col D Date received Date Processed Days taken Are days greater than 3 1/2/3 2/2/3 =b2-a2 if(c23,1,0) 1/2/3 5/2/3 4 1 1/2/3 2/2/3 1 0 2/2/3 5/2/3 3 0 5/2/3 9/2/3 4 1 6/2/3 7/2/3 1 0 etc Cell E1 is a start date, Cell E2 is a stop date. Problem is what formula would I put in cell E3 to count the number of incident between the start and the stop date where Col D for the relevant entries are 1? Any help is appreciated. s-t-m -- sonic-the-mouse |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
So what date column should be checked against the start and stop dates in E1
and E2? The Date Received or the Date Processed? Or both? Need a better explanation. -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ... I am looking to solve the following problem Col A Col B Col C Col D Date received Date Processed Days taken Are days greater than 3 1/2/3 2/2/3 =b2-a2 if(c23,1,0) 1/2/3 5/2/3 4 1 1/2/3 2/2/3 1 0 2/2/3 5/2/3 3 0 5/2/3 9/2/3 4 1 6/2/3 7/2/3 1 0 etc Cell E1 is a start date, Cell E2 is a stop date. Problem is what formula would I put in cell E3 to count the number of incident between the start and the stop date where Col D for the relevant entries are 1? Any help is appreciated. s-t-m -- sonic-the-mouse |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
Sorry for omitting that part, I would be calculating the dates in Column A, so if the start date was 1/2/3 and the end date was 2/2/3 it would return the answer 1 (as there is only 1 incident between these dates which took longer than 3 days to process. If the start date was 1/2/3 and the end date was 6/2/3 then it would return 2. Hope this helps. s-t-m T. Valko;3358856 Wrote: So what date column should be checked against the start and stop dates in E1 and E2? The Date Received or the Date Processed? Or both? Need a better explanation. -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ...- I am looking to solve the following problem Col A Col B Col C Col D Date received Date Processed Days taken Are days greater than 3 1/2/3 2/2/3 =b2-a2 if(c23,1,0) 1/2/3 5/2/3 4 1 1/2/3 2/2/3 1 0 2/2/3 5/2/3 3 0 5/2/3 9/2/3 4 1 6/2/3 7/2/3 1 0 etc Cell E1 is a start date, Cell E2 is a stop date. Problem is what formula would I put in cell E3 to count the number of incident between the start and the stop date where Col D for the relevant entries are 1? Any help is appreciated. s-t-m -- sonic-the-mouse - -- sonic-the-mouse |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
Try this:
=SUMPRODUCT(--(A2:A10=E1),--(A2:A10<=E2),--(D2:D10=1)) -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ... Sorry for omitting that part, I would be calculating the dates in Column A, so if the start date was 1/2/3 and the end date was 2/2/3 it would return the answer 1 (as there is only 1 incident between these dates which took longer than 3 days to process. If the start date was 1/2/3 and the end date was 6/2/3 then it would return 2. Hope this helps. s-t-m T. Valko;3358856 Wrote: So what date column should be checked against the start and stop dates in E1 and E2? The Date Received or the Date Processed? Or both? Need a better explanation. -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ...- I am looking to solve the following problem Col A Col B Col C Col D Date received Date Processed Days taken Are days greater than 3 1/2/3 2/2/3 =b2-a2 if(c23,1,0) 1/2/3 5/2/3 4 1 1/2/3 2/2/3 1 0 2/2/3 5/2/3 3 0 5/2/3 9/2/3 4 1 6/2/3 7/2/3 1 0 etc Cell E1 is a start date, Cell E2 is a stop date. Problem is what formula would I put in cell E3 to count the number of incident between the start and the stop date where Col D for the relevant entries are 1? Any help is appreciated. s-t-m -- sonic-the-mouse - -- sonic-the-mouse |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
Tried formula below however it just returns zero as an answer no matter what dates are put in. Any other advice T. Valko;3359458 Wrote: [color=blue][i] Try this: =SUMPRODUCT(--(A2:A10=E1),--(A2:A10=E2),--(D2:D10=1)) -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ... -- sonic-the-mouse |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
That usually means your dates aren't true Excel dates or the dates you
entered in E1:E2 aren't true Excel dates. In Excel a date is just a number formatted to look like a date. So, if you enter a true Excel date in a cell: A1 = 1/1/2009 You can test the date to see if it is in fact a true Excel date: =ISNUMBER(A1) Will return TRUE if the entry is a true Excel date. If it retunrs FALSE then it's not a true Excel date and you need to fix the problem. -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ...[color=blue][i] Tried formula below however it just returns zero as an answer no matter what dates are put in. Any other advice T. Valko;3359458 Wrote: Try this: =SUMPRODUCT(--(A2:A10=E1),--(A2:A10=E2),--(D2:D10=1)) -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ... -- sonic-the-mouse |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
Checked all dates and they are true dates in excel, however SUMPRODUCT is still returning zeros. In F1 I am using =COUNTIF(a2:a10,"="&E1)-COUNTIF(a2:a10,""&e2) to return the number of incidents between the dates, but still cannot work out how to find out how many of those incidents have taken longer than 3 days to process. Any advice? s-t-m T. Valko;3360011 Wrote: That usually means your dates aren't true Excel dates or the dates you entered in E1:E2 aren't true Excel dates. In Excel a date is just a number formatted to look like a date. So, if you enter a true Excel date in a cell: A1 = 1/1/2009 You can test the date to see if it is in fact a true Excel date: =ISNUMBER(A1) Will return TRUE if the entry is a true Excel date. If it retunrs FALSE then it's not a true Excel date and you need to fix the problem. -- Biff Microsoft Excel MVP "sonic-the-mouse" wrote in message ...- Tried formula below however it just returns zero as an answer no matter what dates are put in. Any other advice ...-- -- sonic-the-mouse - -- sonic-the-mouse |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
Been playing about with the SUMPRODUCT formula and input =SUMPRODUCT(--(A2:A10=E1),--(A2:A10<=E2),--(D2:D10=1)) The < signs appear to be returning the correct answer! So I fully understand the formula can you explain what the "--" signifies, please? s-t-m sonic-the-mouse;3360456 Wrote: Checked all dates and they are true dates in excel, however SUMPRODUCT is still returning zeros. In F1 I am using =COUNTIF(a2:a10,"="&E1)-COUNTIF(a2:a10,""&e2) to return the number of incidents between the dates, but still cannot work out how to find out how many of those incidents have taken longer than 3 days to process. Any advice? s-t-m -- sonic-the-mouse |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search between dates & return other column
http://xldynamic.com/source/xld.SUMPRODUCT.html
http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- David Biddulph "sonic-the-mouse" wrote in message ... Been playing about with the SUMPRODUCT formula and input =SUMPRODUCT(--(A2:A10=E1),--(A2:A10<=E2),--(D2:D10=1)) The < signs appear to be returning the correct answer! So I fully understand the formula can you explain what the "--" signifies, please? s-t-m sonic-the-mouse;3360456 Wrote: Checked all dates and they are true dates in excel, however SUMPRODUCT is still returning zeros. In F1 I am using =COUNTIF(a2:a10,"="&E1)-COUNTIF(a2:a10,""&e2) to return the number of incidents between the dates, but still cannot work out how to find out how many of those incidents have taken longer than 3 days to process. Any advice? s-t-m -- sonic-the-mouse |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a column value from a horizontal search | Excel Worksheet Functions | |||
Search a column for values, return a value from adj column | Excel Worksheet Functions | |||
Search, Match, And return corresponding column value | Excel Worksheet Functions | |||
Search a column for a value and return T or F | Excel Discussion (Misc queries) | |||
Search column for value and return TRUE or FALSE | Excel Worksheet Functions |