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

score 4 · Answer 1 · answered Mar 06 '17 at 14:50

4

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.

answered Mar 06 '17 at 14:50

Scott Craner

148,073
10
49
81

that looks like it would do exactly what I need, but when I put in the formula I get a reference error – premiumcopypaper Mar 06 '17 at 14:58
What excel are you using? – Scott Craner Mar 06 '17 at 15:03
Do you use `;` instead of `,` in your formulas? Are your dates true dates and not text that look like dates? There are many many reasons the formula will not work. Look at your data and at the method in which you enter formulas. – Scott Craner Mar 06 '17 at 15:10

score 0 · Accepted Answer · answered Mar 06 '17 at 18:26

0

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

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

answered Mar 06 '17 at 18:26

premiumcopypaper

165
1
13

Calculate work days

2 Answers2