Formula - HHHHHEELLLPP

[K]

Guys I'm at my wits end. I had to come up with a formula that had to go
through multiple criteria and then had to calculate network days between 2
days for a huge set of data, after extracting the date from an alpha numeric
cell. I can't use networkdays, so please don't suggest it, I basically found
a formula that wrote out the entire network days forumula.

=SUM(IF((B15:B19<>""),IF((($F$15:$F$19>='Sheet1'!$AP$14)*($F$15:$F$19<='Sheet1'!$AP$15)*($AB$15:$AB$19<>"X")*($AB$15:$AB$19<>"")*(LEFT($C$15:$C$19,2)="RA")*($A$15:$A$19="C")),(((DATEVALUE(MID(AB$15:$AB$19,FIND("
",$AB$15:$AB$19)+1,99)))-$E$15:$E$19)+1-SUM(INT(((DATEVALUE(MID($AB$15:$AB$19,FIND("
",$AB$15:$AB$19)+1,99)))-{1,7})/7)-INT(($E$15:$E$19-{1,7}-1)/7))-COUNTIF(Holidays,">="&$E$15:$E$19)+COUNTIF(Holidays,">"&(DATEVALUE(MID($AB$15:$AB$19,FIND(" ",$AB$15:$AB$19)+1,99))))))))

Column F = project month end date
Column E = start date
Column AB = alpha numeric data for example (X, AL 7/15/08, OR NULL) for
individual
Column C = type
Column A = status
Column B = ID#

My logic for formula is
for all IDs not = blank, and project month end date >= july 1 and <=july 31,
Type = RA, status = C, Column AB <> X or null,
strip out 7/15/08 from "AL 7/15/08", and give me the networkdays between
start date and 7/15/08.

Repeat for rows 15-19.

I have only one...just one problem that I can't figure out for the life of me.
My formula for some reason is skipping over the first line of my data set,
i.e. if the range is from 15-19, its taking 16-19....help!

 

[smartin]

Guys I'm at my wits end. I had to come up with a formula that had to go
through multiple criteria and then had to calculate network days between 2
days for a huge set of data, after extracting the date from an alpha numeric
cell. I can't use networkdays, so please don't suggest it, I basically found
a formula that wrote out the entire network days forumula.

=SUM(IF((B15:B19<>""),IF((($F$15:$F$19>='Sheet1'!$AP$14)*($F$15:$F$19<='Sheet1'!$AP$15)*($AB$15:$AB$19<>"X")*($AB$15:$AB$19<>"")*(LEFT($C$15:$C$19,2)="RA")*($A$15:$A$19="C")),(((DATEVALUE(MID(AB$15:$AB$19,FIND("
",$AB$15:$AB$19)+1,99)))-$E$15:$E$19)+1-SUM(INT(((DATEVALUE(MID($AB$15:$AB$19,FIND("
",$AB$15:$AB$19)+1,99)))-{1,7})/7)-INT(($E$15:$E$19-{1,7}-1)/7))-COUNTIF(Holidays,">="&$E$15:$E$19)+COUNTIF(Holidays,">"&(DATEVALUE(MID($AB$15:$AB$19,FIND(" ",$AB$15:$AB$19)+1,99))))))))

Column F = project month end date
Column E = start date
Column AB = alpha numeric data for example (X, AL 7/15/08, OR NULL) for
individual
Column C = type
Column A = status
Column B = ID#

My logic for formula is
for all IDs not = blank, and project month end date >= july 1 and <=july 31,
Type = RA, status = C, Column AB <> X or null,
strip out 7/15/08 from "AL 7/15/08", and give me the networkdays between
start date and 7/15/08.

Repeat for rows 15-19.

I have only one...just one problem that I can't figure out for the life of me.
My formula for some reason is skipping over the first line of my data set,
i.e. if the range is from 15-19, its taking 16-19....help!

Holy camoley man. That formula is ... beyond my patience to parse. I
suggest you decompose it into helper columns of intermediate results.
You seem to have a lot of repeating elements that might make this easier
to debug in this way.

As a SWAG, I would investigate the output from the INT() formulas, as
INT() can create "off by one errors" if not used carefully.