Networkdays for delay calculation without weekends

[KeLo]

Hi all, I am new to the forum and newish to Excel. I am having trouble calculating delay based on two dates when the second date is earlier than the first when using Networkdays. You can see in my example the formula returns -3 instead of -1. Thanks for your help!

 

[bebo021999]

Excel is smart enough to treat networkdays with loss in the past (end date is < start date) in opposite with in the future, then use:

=NETWORKDAYS(AB43,AD43)-IF(AD43>=AB43,1,-1)

 

[fadee2]

Hi Kelo,
Welcome to the board.

for your query, excel is returning -3 because:
1. Networkdays counts the number of days in between start date and end date. To overcome this, single day is deducted using -1.
2. Since your end date is less then your start date, networkdays tries to deduct your start date from end date and results in -2,
3. but since you already have a -1 in your formula, excel sums up -2 and -1 and returns -3.

to solve this you should wrap all your formula in an if statement like below:

=IF(B1>=A1,NETWORKDAYS(A1,B1)-1,NETWORKDAYS(A1,B1)+1)

The condition will first check if end date is greater or less then the start date. If it is less then the start date then -1 will be applied. Else if it is greater then start date then the formula will add 1 to your result.

HTH...

 

[KeLo]

Excel is smart enough to treat networkdays with loss in the past (end date is < start date) in opposite with in the future, then use:

=NETWORKDAYS(AB43,AD43)-IF(AD43>=AB43,1,-1)

Thanks for the answer!