Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.



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

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.


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

Networkdays does not account for saturdays and sundays e.g. start day: Friday End-day: Monday (networkdays-1 will result in '1' which is precisely what you want) but when start-day: Satday or Sunday End-day: Monday (networkdays-1 will result in '0' which is not the correct answer) we have to adjust for these weekends by ourselves using weekday function.

share|improve this answer
I don't have Excel 2007 but that's not the way it works in other versions of Excel, plus even if it does work that way in 2007, this isn't an answer, it's a best a comment. – blm Jan 17 at 18:41

Your Answer


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.