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??

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??

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??

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??

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??

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??