| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date, networkdays and sumproduct
I have a major formula that I'm trying to figure out and I needed some help.
Column A= ID#s Column B= Status Column C= End dates Column D=Responses Column D responses are as follows: AL 7/8/08 NI 8/9/08 WL 6/20/08 RCC 7/8/08 WLC 5/15/08 Each cell in a column can have only one of these responses. I basically want to do the following: For all ID#'s (Column A): If status (column B) = "Closed" AND If first 2 letters of (column D) = "AL" Extract date out. Find out the # of network days between extracted date and end date (column C) and count them if the # of network days <=5 Repeat for all ID#s I know I will be using sumproduct, but how do I extract dates, convert them to date format, find the networdays? Can I use sumproduct with networkdays?? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract date, networkdays and sumproduct
Here is an array formula
=SUM(IF(B2:B20<"",IF((B2:B20="Closed")*(C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)=5),C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)))) -- __________________________________ HTH Bob "K" wrote in message ... I have a major formula that I'm trying to figure out and I needed some help. Column A= ID#s Column B= Status Column C= End dates Column D=Responses Column D responses are as follows: AL 7/8/08 NI 8/9/08 WL 6/20/08 RCC 7/8/08 WLC 5/15/08 Each cell in a column can have only one of these responses. I basically want to do the following: For all ID#'s (Column A): If status (column B) = "Closed" AND If first 2 letters of (column D) = "AL" Extract date out. Find out the # of network days between extracted date and end date (column C) and count them if the # of network days <=5 Repeat for all ID#s I know I will be using sumproduct, but how do I extract dates, convert them to date format, find the networdays? Can I use sumproduct with networkdays?? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract date, networkdays and sumproduct
Question: why do you do +199?
I see that you are ommitting the ones that are greater than 5, but by doing a plain subtraction we can't eliminate weekends or any predefined holidays. "Bob Phillips" wrote: Here is an array formula =SUM(IF(B2:B20<"",IF((B2:B20="Closed")*(C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)=5),C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)))) -- __________________________________ HTH Bob "K" wrote in message ... I have a major formula that I'm trying to figure out and I needed some help. Column A= ID#s Column B= Status Column C= End dates Column D=Responses Column D responses are as follows: AL 7/8/08 NI 8/9/08 WL 6/20/08 RCC 7/8/08 WLC 5/15/08 Each cell in a column can have only one of these responses. I basically want to do the following: For all ID#'s (Column A): If status (column B) = "Closed" AND If first 2 letters of (column D) = "AL" Extract date out. Find out the # of network days between extracted date and end date (column C) and count them if the # of network days <=5 Repeat for all ID#s I know I will be using sumproduct, but how do I extract dates, convert them to date format, find the networdays? Can I use sumproduct with networkdays?? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract date, networkdays and sumproduct
I don't, it is +1, 99
Forgot the networkdays, that will need more work as Networkdays doesn't work in an array formula. -- __________________________________ HTH Bob "K" wrote in message ... Question: why do you do +199? I see that you are ommitting the ones that are greater than 5, but by doing a plain subtraction we can't eliminate weekends or any predefined holidays. "Bob Phillips" wrote: Here is an array formula =SUM(IF(B2:B20<"",IF((B2:B20="Closed")*(C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)=5),C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)))) -- __________________________________ HTH Bob "K" wrote in message ... I have a major formula that I'm trying to figure out and I needed some help. Column A= ID#s Column B= Status Column C= End dates Column D=Responses Column D responses are as follows: AL 7/8/08 NI 8/9/08 WL 6/20/08 RCC 7/8/08 WLC 5/15/08 Each cell in a column can have only one of these responses. I basically want to do the following: For all ID#'s (Column A): If status (column B) = "Closed" AND If first 2 letters of (column D) = "AL" Extract date out. Find out the # of network days between extracted date and end date (column C) and count them if the # of network days <=5 Repeat for all ID#s I know I will be using sumproduct, but how do I extract dates, convert them to date format, find the networdays? Can I use sumproduct with networkdays?? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract date, networkdays and sumproduct
So what's the purpose of the +1,99?
Also could a countif work here? Can networkdays work with that? "Bob Phillips" wrote: I don't, it is +1, 99 Forgot the networkdays, that will need more work as Networkdays doesn't work in an array formula. -- __________________________________ HTH Bob "K" wrote in message ... Question: why do you do +199? I see that you are ommitting the ones that are greater than 5, but by doing a plain subtraction we can't eliminate weekends or any predefined holidays. "Bob Phillips" wrote: Here is an array formula =SUM(IF(B2:B20<"",IF((B2:B20="Closed")*(C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)=5),C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)))) -- __________________________________ HTH Bob "K" wrote in message ... I have a major formula that I'm trying to figure out and I needed some help. Column A= ID#s Column B= Status Column C= End dates Column D=Responses Column D responses are as follows: AL 7/8/08 NI 8/9/08 WL 6/20/08 RCC 7/8/08 WLC 5/15/08 Each cell in a column can have only one of these responses. I basically want to do the following: For all ID#'s (Column A): If status (column B) = "Closed" AND If first 2 letters of (column D) = "AL" Extract date out. Find out the # of network days between extracted date and end date (column C) and count them if the # of network days <=5 Repeat for all ID#s I know I will be using sumproduct, but how do I extract dates, convert them to date format, find the networdays? Can I use sumproduct with networkdays?? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract date, networkdays and sumproduct
The +1, 99 is to get the first character after a space, and get up to 99
characters (in reality there will be far less). Functions like COUNTIF won't work with embedded functions in the lookup ranges. -- __________________________________ HTH Bob "K" wrote in message ... So what's the purpose of the +1,99? Also could a countif work here? Can networkdays work with that? "Bob Phillips" wrote: I don't, it is +1, 99 Forgot the networkdays, that will need more work as Networkdays doesn't work in an array formula. -- __________________________________ HTH Bob "K" wrote in message ... Question: why do you do +199? I see that you are ommitting the ones that are greater than 5, but by doing a plain subtraction we can't eliminate weekends or any predefined holidays. "Bob Phillips" wrote: Here is an array formula =SUM(IF(B2:B20<"",IF((B2:B20="Closed")*(C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)=5),C2:C20-MID(D2:D20,FIND(" ",D2:D20)+1,99)))) -- __________________________________ HTH Bob "K" wrote in message ... I have a major formula that I'm trying to figure out and I needed some help. Column A= ID#s Column B= Status Column C= End dates Column D=Responses Column D responses are as follows: AL 7/8/08 NI 8/9/08 WL 6/20/08 RCC 7/8/08 WLC 5/15/08 Each cell in a column can have only one of these responses. I basically want to do the following: For all ID#'s (Column A): If status (column B) = "Closed" AND If first 2 letters of (column D) = "AL" Extract date out. Find out the # of network days between extracted date and end date (column C) and count them if the # of network days <=5 Repeat for all ID#s I know I will be using sumproduct, but how do I extract dates, convert them to date format, find the networdays? Can I use sumproduct with networkdays?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Networkdays with no start date and no end date | Excel Worksheet Functions | |||
| Using booleans in sumproduct formulas to extract boolean range | Excel Worksheet Functions | |||
| Can SUMPRODUCT be used to extract varying data in a column? | Excel Worksheet Functions | |||
| How do i add networkdays to a date? | Excel Discussion (Misc queries) | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Linear Mode
