Skipping Blank cells in formula(searched but cant figure how to apply)

[scotinexcile]

Hi,

I am using a formula to calculate number of "work" days in a range of start and end dates. Not sure where the formula came from and need to modify it It is used to calculate the number of "workdays" in a range of dates that dont overlap.

=SUM(IF(MMULT((WEEKDAY(ROW(INDIRECT(MIN(INT(B14:B20))&":"&MAX(INT(C14:C20)))),2)<6)*(ROW(INDIRECT(MIN( INT(B14:B20))&":"&MAX(INT(C14:C20))))>=TRANSPOSE(INT(B14:B20)))*(ROW(INDIRECT(MIN(INT(B14:B20))&":"&MAX(INT(C14:C20))))<=TRANSPOSE(C14:C20))+0,ROW(B14:B20)^0),1))


13 Planned Start Date Planned Finish Date
14 8/16/2012 8/20/2012
15 8/19/2012 8/24/2012
16 8/21/2012 8/29/2012
17 9/1/2012 9/12/2012
18 10/2/2012 10/4/2012
19 11/1/2012 11/2/2012
20 11/2/2012 11/13/2012

I need to extend the Range to B14:B100 and C14:C100 but the cells may not have data in them, so the formula throws an error. I need to modify the formula to ignore the blank cells

I have tried using the <> "" after each range reference(B14:B20, <> "") and (C14:C20, <> "") but still not working for me, I am not sure what I am doing wrong

Thank you in advance for any advice you can give me

[NBVC]

That looks like a Domenic formula...

If the blank cells are not intermingled with the cells containing data, the best thing would be to create dynamic named ranges so that the formula only uses what it needs to.

If that is okay, then assuming the blanks are not results of formulas, you can try a named Range, like MyRange with formula as source like:

=Sheet4!$B$14:INDEX(Sheet4!$C:$C,MATCH(REPT("z",255),Sheet4!$B:$B)) if text in column B or

=Sheet4!$B$14:INDEX(Sheet4!$C:$C,MATCH(10^10,Sheet4!$B:$B)) if numbers in column B.

then replace each occurance of B14:B20 in your formula with INDEX(MyRange,0,1) and each occurance of C14:C20 with INDEX(MyRange,0,2)

[scotinexcile]

I guess the person I inherited the sheet from got it from Domenic thanks Domenic

The blank cells would be at the "end" of the filled cells. So B21, B22.... B100 will all be blank, until the employee enters a new project in the next blank line. In the case above, line 21

The end result "my boss" is looking for is to see is a employee is over or under utilized for a time period. So employee has 40hrs in a week, if they have 2 projects that start and finish in the same week that total 40 hrs they are 100% utilized. If they have 1 project for a week time period(5 working days) that totals 20 hours then they are 50% utilized.

I am taking the results and plotting them in a bar chart. The boss likes pretty pictures

Thanks for the help

[scotinexcile]

Also the Cells B14 to D20 are formatted as Dates not numbers or txt, not sure if it matters or not

[scotinexcile]

So it should look like this?


=SUM(IF(MMULT((WEEKDAY(ROW(INDIRECT(MIN(INT(INDEX(Project_Start_Date,0,1)))&":"&MAX(INT(INDEX(Project_End_Date,0,1))))),2)<6)*(ROW(INDIRECT(MIN( INT(INDEX(Project_Start_Date,0,1)))&":"&MAX(INT(INDEX(Project_End_Date,0,1)))))>=TRANSPOSE(INT(INDEX(Project_Start_Date,0,1))))*(ROW(INDIRECT(MIN(INT(INDEX(Project_Start_Date,0,1)))&":"&MAX(INT(INDEX(Project_End_Date,0,1)))))<=TRANSPOSE(INDEX(Project_End_Date,0,1)))+0,ROW(INDEX(Project_Start_Date,0,1))^0),1))

[Ron Coderre]

using your sample data in A1:C10

Please Login or Register  to view this content.

and
a holiday list in L1:L10

This regular formula returns the total number of workdays for each of those date ranges

Please Login or Register  to view this content.

With that data (and no holidays), the formula returns: 36 <---corrected a typo here

Using this data, instead:

Please Login or Register  to view this content.

Now the formula returns: 26
because two more date ranges are invalid.

Is that something you can work with?

[NBVC]

So it should look like this?


=SUM(IF(MMULT((WEEKDAY(ROW(INDIRECT(MIN(INT(INDEX(Project_Start_Date,0,1)))&":"&MAX(INT(INDEX(Project_End_Date,0,1))))),2)<6)*(ROW(INDIRECT(MIN( INT(INDEX(Project_Start_Date,0,1)))&":"&MAX(INT(INDEX(Project_End_Date,0,1)))))>=TRANSPOSE(INT(INDEX(Project_Start_Date,0,1))))*(ROW(INDIRECT(MIN(INT(INDEX(Project_Start_Date,0,1)))&":"&MAX(INT(INDEX(Project_End_Date,0,1)))))<=TRANSPOSE(INDEX(Project_End_Date,0,1)))+0,ROW(INDEX(Project_Start_Date,0,1))^0),1))

Did you create 2 named ranges? one form column B, and one for column C?

if so, then you don't need those'll work, although you could also just reference the NamedRange and not need the INDEX() part...

and if B and C are dates, then sample Named Range formula:

=Sheet4!$B$14:INDEX(Sheet4!$B:$B,MATCH(10^10,Sheet4!$B:$B))

where Sheet4 is the name of the sheet.

similar for column C

[scotinexcile]

Thanks for the help

I got this to work =SUMPRODUCT((C2:C100>0)*(D2:D100>0),NETWORKDAYS(OFFSET(C1,ROW(INDIRECT("1:99")),,),OFFSET(D1,ROW(INDIRECT("1:99")),),L1:L10))

[NBVC]

separate thread please...

[scotinexcile]

thanks, wanted to check how the forum likes to work. I will delete teh above and post in a different thread

[daddylonglegs]

I got this to work =SUMPRODUCT((C2:C100>0)*(D2:D100>0),NETWORKDAYS(OFFSET(C1,ROW(INDIRECT("1:99")),,),OFFSET(D1,ROW(INDIRECT("1:99")),),L1:L10))

....but this doesn't really do the same thing as the original formula you quoted. The original doesn't count any dates twice, the above version will double count if there are overlaps, so you get 36 for your example instead of 30 (there are only 30 unique workdays within those date ranges).

This formula should give you the same results as the original but also copes with blank rows (I assume that rows either have both dates or none and that entries are date without times)

=NETWORKDAYS(MIN(B2:B100),MAX(C2:C100),IF(MMULT((ROW(INDIRECT(MIN(C2:C100)&":"&MAX(B2:B100)))>=TRANSPOSE(B2:B100))*(ROW(INDIRECT(MIN(C2:C100)&":"&MAX(B2:B100)))<=TRANSPOSE(C2:C100)),ROW(B2:B100)^0),0,ROW(INDIRECT(MIN(C2:C100)&":"&MAX(B2:B100)))))

confirmed with CTRL+SHIFT+ENTER

[scotinexcile]

It just gives me a #Value error? Thank you for your time

[daddylonglegs]

I used the range from row 2 to row 100 but perhaps you have some text data in rows 2 to 13? Try referencing just rows 14 to 100, i.e.

=NETWORKDAYS(MIN(B14:B100),MAX(C14:C100),IF(MMULT((ROW(INDIRECT(MIN(C14:C100)&":"&MAX(B14:B100)))>=TRANSPOSE(B14:B100))*(ROW(INDIRECT(MIN(C14:C100)&":"&MAX(B14:B100)))<=TRANSPOSE(C14:C100)),ROW(B14:B100)^0),0,ROW(INDIRECT(MIN(C14:C100)&":"&MAX(B14:B100)))))

The formula also needs to be "array entered" (as does your original). Select the formula cell then press F2 to select the formula, hold down CTRL and SHIFT keys and press ENTER so that you get curly braces around the formula.

...I also realised that it's the INT functions in your original formula that prevent it working on blank rows, you don't really need the INT functions if your cells are just dates (can you confirm that they have dates without times?), so this version should also work (also "array entered")

=SUM(IF(MMULT((WEEKDAY(ROW(INDIRECT(MIN(B14:B100)&":"&MAX(C14:C100))),2)<6)*(ROW(INDIRECT(MIN(B14:B100)&":"&MAX(C14:C100)))>=TRANSPOSE(B14:B100))*(ROW(INDIRECT(MIN(B14:B100)&":"&MAX(C14:C100)))<=TRANSPOSE(C14:C100))+0,ROW(B14:B100)^0),1))

see example attached - those 2 formula in H3 and H4

[scotinexcile]

Yes only dates in the fields NO times. Thank you again for your time I will give this a try as soon as I can

[scotinexcile]

Here is what I ended up with

Book has a sheet for each Employee to enter their Project Start and End dates

I used the summary sheet to quickly display the "utilization" of each employee

Would still like to be able to show Utilization by week but am stumped on figuring out how to split the days worked into the correct week of the year!!!

Thanks to everyone for their help, much appreciated Wendy1Blank.xlsx