Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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.

=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

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .