NETWORKDAYS help please!
- Thread starter D.L.
- Start date
thank you, Debraj! that formula worked. now my problem is that i need to add other NETWORKDAYS to the formula and exclude the Holidays. this is what i have but doesn't seem to be working:
=NETWORKDAYS(A1,IF(ISNUMBER(B1),B1,C1))+NETWORKDAYS(C1,D1)+NETWORKDAYS(E1,F1),'State Holidays'!$C$2:$C$15)
=NETWORKDAYS(A1,IF(ISNUMBER(B1),B1,C1))+NETWORKDAYS(C1,D1)+NETWORKDAYS(E1,F1),'State Holidays'!$C$2:$C$15)
sample file attached. what i'm doing is creating a macro that will calculate the "# of days to process" but have me select a spreadsheet containing holidays (State Holidays) and exclude any holidays for the month from the "# of days to process". the formula that i have below is incorrect cause i get an error.
=NETWORKDAYS(A1,IF(ISNUMBER(B1),B1,C1))+NETWORKDAYS(C1,D1)+NETWORKDAYS(E1,F1),'State Holidays'!$C$2:$C$15)
=NETWORKDAYS(A1,IF(ISNUMBER(B1),B1,C1))+NETWORKDAYS(C1,D1)+NETWORKDAYS(E1,F1),'State Holidays'!$C$2:$C$15)
all columns are relevant. i did do the above formula and worked fine but when i included the extra NETWORKDAYS (between columns C2 and D2 and between E2 and F2) to the formula to get a total sum for the "# of days to process" i receive an error: "run-time error '1004': application-defined or object-defined error".
ok, i somewhat figured it out. i have:
=NETWORKDAYS(A2,IF(ISNUMBER(B2),B2,C2),'State Holidays'!$C$2:$C$20)+NETWORKDAYS(C2,D2,'State Holidays'!$C$2:$C$20)+NETWORKDAYS(E2,F2,'State Holidays'!$C$2:$C$20)
the 'State Holidays' is a separate spreadsheet containing holidays that it brings in to exclude in the # DAYS TO PROCESS.
the problem that i'm seeing now is that on Row4 my formula above will calculate the #DAYS TO PROCESS as 3 days but it should only be 1 because the same date is all the way across. how can i get the formula to work to where it will calculate dates in columns A and B or C, then see that it's the same date between C and D so ignore it, then see that it's the same date between E and F so ignore it, and so in the end it will show as 1 PROCESS DAY but it will also calculate the correct #DAYS TO PROCESS for the other Rows? i hope that makes sense?
=NETWORKDAYS(A2,IF(ISNUMBER(B2),B2,C2),'State Holidays'!$C$2:$C$20)+NETWORKDAYS(C2,D2,'State Holidays'!$C$2:$C$20)+NETWORKDAYS(E2,F2,'State Holidays'!$C$2:$C$20)
the 'State Holidays' is a separate spreadsheet containing holidays that it brings in to exclude in the # DAYS TO PROCESS.
the problem that i'm seeing now is that on Row4 my formula above will calculate the #DAYS TO PROCESS as 3 days but it should only be 1 because the same date is all the way across. how can i get the formula to work to where it will calculate dates in columns A and B or C, then see that it's the same date between C and D so ignore it, then see that it's the same date between E and F so ignore it, and so in the end it will show as 1 PROCESS DAY but it will also calculate the correct #DAYS TO PROCESS for the other Rows? i hope that makes sense?
NARAYANK991
Excel Ninja
Hi ,
Can you clarify the following ?
You have only one output , in column G ( G2 through G5 ) ; what is this output supposed to represent ?
Even though there are intermediate stages of processing , why can you not take the number of working days between the final stage in column F , and the initial stage in column A ? Why do you have to add 3 intermediate results to get the final output ?
Narayan
Can you clarify the following ?
You have only one output , in column G ( G2 through G5 ) ; what is this output supposed to represent ?
Even though there are intermediate stages of processing , why can you not take the number of working days between the final stage in column F , and the initial stage in column A ? Why do you have to add 3 intermediate results to get the final output ?
Narayan
thanks for your reply...
column G represents the total #DAYS OF PROCESS that it takes an application to go thru various departments. I need 1 formula that will calculate this. so from column A to column B or C it would be # of days to process, then from column C to Column D it would be another # of days to process, and from column E to column F it would be another # of days to process and Column G would be the Grand Total #DAYS TO PROCESS. on row4 I could do just column A to F because it's the same date across but I need one formula that would see not just that but the other instances described above (A to B/C, C to D, E to F). I'm not sure if it's even possible???
column G represents the total #DAYS OF PROCESS that it takes an application to go thru various departments. I need 1 formula that will calculate this. so from column A to column B or C it would be # of days to process, then from column C to Column D it would be another # of days to process, and from column E to column F it would be another # of days to process and Column G would be the Grand Total #DAYS TO PROCESS. on row4 I could do just column A to F because it's the same date across but I need one formula that would see not just that but the other instances described above (A to B/C, C to D, E to F). I'm not sure if it's even possible???