Calculate work days

Question

I want to calculate work days between two dates. NETWORKDAYS will do the trick, however I am having difficulty structuring the IF statement components.

I have 5 columns, Column A will always have a start date; the other 4 columns might not. What I want to calculate is the network days between Column A and B, but if B is blank then Column A and C, and if C is blank, then Column A and D and so forth

Answer 1

=IF(A1<>"",NETWORKDAYS(A1, B1), IF(C1<>"", NETWORKDAYS(A1, C1)))

This does the trick, I could not get AGGREGATE/INDEXto work

Answer 2

Use this formula:

=NETWORKDAYS(A1,INDEX(A1:E1,AGGREGATE(15,6,COLUMN(B1:E1)/(B1:E1<>""),1)))

The INDEX/AGGREGATE will find the first cell in B:E that is not empty and return that as the end date to the NETWORKDAYS.