G
Guest
I am in the last stretch of my project for work. I am hung up on one tiny detail. I couldn't figure out how to upload my spreadsheet so I'll have to explain. I am currently using this formula: =IF(E3=0,"",NETWORKDAYS(B3,E3,Holiday))
to calculate the work days that pass between the beginning of a project and the end of the project. The start day, however, can not be counted in the total calculation.
I need help with a formula that will return these results:
Start Finish Total Days
12/8 12/11 3
12/8 Shouldn't calculate because there is no data in "Finish"
12/8 12/8 1
12/8 12/9 1
The problem I am running into is that 12/8 - 12/11 is calculated as 4 days. If I just subtract 1 from the formula than I get inaccurate data in the 12/8-12/8 and 12/8-12/9 scenarios. The importance of accurately returned data is I have another formula calculating the average values returned in "Total Days".
I tried this formula:
=IF(E13=0,"",NETWORKDAYS(B9,E9-1,Holiday)) and this version
=IF(E13=0,"",NETWORKDAYS(B9,E9,'Holiday )-1)
and then I tried a version of the above with another "if" saying that if "Start" and "Finish" equal each other than "Total Days" =1.
All of these versions somehow mess up my Average formula. =AVERAGE(IF(F3:F562>0,F3:F562))
The Average formula was written so that 0 values aren't calculated in the average. I had to do it this way because despite unchecking the "Show Zero Values" box, Excel still sees the 0 value in the entire range of cells.
Please help
Matt
to calculate the work days that pass between the beginning of a project and the end of the project. The start day, however, can not be counted in the total calculation.
I need help with a formula that will return these results:
Start Finish Total Days
12/8 12/11 3
12/8 Shouldn't calculate because there is no data in "Finish"
12/8 12/8 1
12/8 12/9 1
The problem I am running into is that 12/8 - 12/11 is calculated as 4 days. If I just subtract 1 from the formula than I get inaccurate data in the 12/8-12/8 and 12/8-12/9 scenarios. The importance of accurately returned data is I have another formula calculating the average values returned in "Total Days".
I tried this formula:
=IF(E13=0,"",NETWORKDAYS(B9,E9-1,Holiday)) and this version
=IF(E13=0,"",NETWORKDAYS(B9,E9,'Holiday )-1)
and then I tried a version of the above with another "if" saying that if "Start" and "Finish" equal each other than "Total Days" =1.
All of these versions somehow mess up my Average formula. =AVERAGE(IF(F3:F562>0,F3:F562))
The Average formula was written so that 0 values aren't calculated in the average. I had to do it this way because despite unchecking the "Show Zero Values" box, Excel still sees the 0 value in the entire range of cells.
Please help
Matt