Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum the difference between dates given a certain criteria
I have a workbook with several worksheets (tabs)
The first and main worksheet has eight (8) columns with N rows. I wish to calculate the number of days between two date values and sum the results for rows that meet a certain criteria. Lets use a simple example (so I can understand) Three columns as follows: A. Start Date B. Due Date C. Criteria If the word in column C is €śDone€ť than calculate the number of days between A & B and add it to a sum. A B C Start Date Due Date Status 01/01/08 01/31/08 Done 02/01/08 04/15/08 Done 03/01/08 05/10/08 Open 04/01/08 06/15/08 Open 05/02/08 06/15/08 Done Thank you in advance for your help. Detroit David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum the difference between dates given a certain criteria
Try this:
=SUMPRODUCT(--(C2:C6="done"),B2:B6-A2:A6) -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... I have a workbook with several worksheets (tabs) The first and main worksheet has eight (8) columns with N rows. I wish to calculate the number of days between two date values and sum the results for rows that meet a certain criteria. Lets use a simple example (so I can understand) Three columns as follows: A. Start Date B. Due Date C. Criteria If the word in column C is "Done" than calculate the number of days between A & B and add it to a sum. A B C Start Date Due Date Status 01/01/08 01/31/08 Done 02/01/08 04/15/08 Done 03/01/08 05/10/08 Open 04/01/08 06/15/08 Open 05/02/08 06/15/08 Done Thank you in advance for your help. Detroit David |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum the difference between dates given a certain criteria
Thank you for the prompt and accurate reply it worked perfectly.... however I
forgot one element which I have not been able to get to work. I wish to calculate using "NETWORKDAYS" I have tried everyway I can think of to get the formula to work with "NETWORKDAYS" but I haven't found the solution. Could you please tell me how to use NETWORKDAYS in this type of calculation. Thank you "T. Valko" wrote: Try this: =SUMPRODUCT(--(C2:C6="done"),B2:B6-A2:A6) -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... I have a workbook with several worksheets (tabs) The first and main worksheet has eight (8) columns with N rows. I wish to calculate the number of days between two date values and sum the results for rows that meet a certain criteria. Lets use a simple example (so I can understand) Three columns as follows: A. Start Date B. Due Date C. Criteria If the word in column C is "Done" than calculate the number of days between A & B and add it to a sum. A B C Start Date Due Date Status 01/01/08 01/31/08 Done 02/01/08 04/15/08 Done 03/01/08 05/10/08 Open 04/01/08 06/15/08 Open 05/02/08 06/15/08 Done Thank you in advance for your help. Detroit David |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum the difference between dates given a certain criteria
NETWORKDAYS won't work with arrays so you'd have to do something like this:
=IF(C2="done",NETWORKDAYS(A2,B2,holidays),"") Copy down as needed. Then use a SUM formula on that column. -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... Thank you for the prompt and accurate reply it worked perfectly.... however I forgot one element which I have not been able to get to work. I wish to calculate using "NETWORKDAYS" I have tried everyway I can think of to get the formula to work with "NETWORKDAYS" but I haven't found the solution. Could you please tell me how to use NETWORKDAYS in this type of calculation. Thank you "T. Valko" wrote: Try this: =SUMPRODUCT(--(C2:C6="done"),B2:B6-A2:A6) -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... I have a workbook with several worksheets (tabs) The first and main worksheet has eight (8) columns with N rows. I wish to calculate the number of days between two date values and sum the results for rows that meet a certain criteria. Lets use a simple example (so I can understand) Three columns as follows: A. Start Date B. Due Date C. Criteria If the word in column C is "Done" than calculate the number of days between A & B and add it to a sum. A B C Start Date Due Date Status 01/01/08 01/31/08 Done 02/01/08 04/15/08 Done 03/01/08 05/10/08 Open 04/01/08 06/15/08 Open 05/02/08 06/15/08 Done Thank you in advance for your help. Detroit David |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum the difference between dates given a certain criteria
Thank you for your help.
"T. Valko" wrote: NETWORKDAYS won't work with arrays so you'd have to do something like this: =IF(C2="done",NETWORKDAYS(A2,B2,holidays),"") Copy down as needed. Then use a SUM formula on that column. -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... Thank you for the prompt and accurate reply it worked perfectly.... however I forgot one element which I have not been able to get to work. I wish to calculate using "NETWORKDAYS" I have tried everyway I can think of to get the formula to work with "NETWORKDAYS" but I haven't found the solution. Could you please tell me how to use NETWORKDAYS in this type of calculation. Thank you "T. Valko" wrote: Try this: =SUMPRODUCT(--(C2:C6="done"),B2:B6-A2:A6) -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... I have a workbook with several worksheets (tabs) The first and main worksheet has eight (8) columns with N rows. I wish to calculate the number of days between two date values and sum the results for rows that meet a certain criteria. Lets use a simple example (so I can understand) Three columns as follows: A. Start Date B. Due Date C. Criteria If the word in column C is "Done" than calculate the number of days between A & B and add it to a sum. A B C Start Date Due Date Status 01/01/08 01/31/08 Done 02/01/08 04/15/08 Done 03/01/08 05/10/08 Open 04/01/08 06/15/08 Open 05/02/08 06/15/08 Done Thank you in advance for your help. Detroit David |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum the difference between dates given a certain criteria
You're welcome!
-- Biff Microsoft Excel MVP "Detroit David" wrote in message ... Thank you for your help. "T. Valko" wrote: NETWORKDAYS won't work with arrays so you'd have to do something like this: =IF(C2="done",NETWORKDAYS(A2,B2,holidays),"") Copy down as needed. Then use a SUM formula on that column. -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... Thank you for the prompt and accurate reply it worked perfectly.... however I forgot one element which I have not been able to get to work. I wish to calculate using "NETWORKDAYS" I have tried everyway I can think of to get the formula to work with "NETWORKDAYS" but I haven't found the solution. Could you please tell me how to use NETWORKDAYS in this type of calculation. Thank you "T. Valko" wrote: Try this: =SUMPRODUCT(--(C2:C6="done"),B2:B6-A2:A6) -- Biff Microsoft Excel MVP "Detroit David" wrote in message ... I have a workbook with several worksheets (tabs) The first and main worksheet has eight (8) columns with N rows. I wish to calculate the number of days between two date values and sum the results for rows that meet a certain criteria. Lets use a simple example (so I can understand) Three columns as follows: A. Start Date B. Due Date C. Criteria If the word in column C is "Done" than calculate the number of days between A & B and add it to a sum. A B C Start Date Due Date Status 01/01/08 01/31/08 Done 02/01/08 04/15/08 Done 03/01/08 05/10/08 Open 04/01/08 06/15/08 Open 05/02/08 06/15/08 Done Thank you in advance for your help. Detroit David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difference of two dates | Excel Worksheet Functions | |||
Difference of two dates | Excel Discussion (Misc queries) | |||
difference between dates | Excel Discussion (Misc queries) | |||
difference between dates | Excel Discussion (Misc queries) | |||
difference between two dates | Excel Discussion (Misc queries) |