Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using the NETWORKDAYS formula to count days between 2 days for a series of different transaction dates. I want the count to exclude weekends and holidays on each transaction date. However, the result doesn't come out as I expected.

For example, the transaction between 1/26 and 1/27 should have been 1 day, but it is counted as 2 days. Is there any easy way to count the days? I have tried to use a simple formula such as =C9-C8, which would give me 1 day. Because I have a large amount of data to work with, it would be very difficult use that approach and excluding weekends and holidays.

=NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)

Example:

Date on Column C    Result on Column F   Row 7: 1/20/10 12:00 AM           
Row 8: 1/26/10 12:00 AM          5       Row 9: 1/27/10 12:00 AM             2
Row10: 1/28/10 12:00 AM          2
share|improve this question

2 Answers 2

I think it is because it includes both the start and the end date in the calculation. So I think you are really close, and just need take your count and minus 1 out of it. Something like this -

=(NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)) - 1

Does this make sense?

share|improve this answer
    
It works perfectly for what I need. Thanks a lot! –  user56517 Nov 23 '10 at 17:37
    
You mind marking this as the answer since it sounds like it fixed your problem? Thanks. –  Ben Jones May 31 '11 at 14:05

NETWORKDAYS counts both days, since both are working days. Think of it as a "how many working days are between 1/26 00:00 am and 1/27 00:00 am" function.

In order to achieve what you want, you could mix that up with an IF function.

IF(NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)>1;NETWORKDAYS(C8,C9,holiday!$A$106:$A$117)-1;1)

There might be a more elegant way to do it, though.

share|improve this answer
    
Thanks a lot for your help. –  user56517 Nov 23 '10 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.