Counting Dates (Complicated)

[scott11]

Hello,

I have the following formula in cell D9: =IF(ISNUMBER($E9),IF(F9>0,F9-E9,TODAY()-E9), " "). This indicates the number of days that have past since my Start Date and stops counting after I input my End Date, however, I would like to modify it to include only NETWORKDAYS. In addition, if I put a date that is past TODAY's current date, I receive a negative number. Ideally, I would prefer cell D9 to have a 0 value, until that future date turns into TODAY's date and then start counting.

My second formula in cell F8 is: =IF(MAX(F9:F13)>0,MAX(F9:F13),""). I do not want this cell to populate a date until either all cells in the range of F9:F13 have a date, or until cell C8 shows "100%" (C8 has the following formula: =SUMPRODUCT(C9:C13)/6.

 

[Joe4]

Does this do what you want?

=IF(ISNUMBER($E9),MAX(IF(F9>0,NETWORKDAYS(E9,F9),NETWORKDAYS(E9,TODAY())),0),"")

 

[scott11]

Does this do what you want?

=IF(ISNUMBER($E9),MAX(IF(F9>0,NETWORKDAYS(E9,F9),NETWORKDAYS(E9,TODAY())),0),"")

Works like a charm, thanks Joe! Any luck with the second one by chance?

 

[Joe4]

Works like a charm, thanks Joe! Any luck with the second one by chance?

Is this what you are after?

=IF(AND(C8=100%,MAX(F9:F13)>0),MAX(F9:F13),"")

By the way, why is your formula in cell C8 divided by 6 and not 5?
There are only 5 cells in that range (C9:C13).