Getting Unexpected Result From NETWORKDAYS when

[nhmiller]

OK - I'm having an issue and the details follow...

The Purpose / Function
OK - so the purpose of this is to calculate the days remaining between the committed completion date for a task and the current date. The lookup checks the task's status column against an array for Active/Inactive status so that the days remaining cell can be set null if the task is inactive (complete, held etc...) and the second (nested if) sets the cell to null if there is no commit date - or calculates the number of days remaining otherwise.

The Formula
=IF(VLOOKUP(X2,Reference!$A$42:$B$56,2)="Inactive","",IF(AA2="","",(NETWORKDAYS(TODAY(),AA2))))

Relevant Columns / Variables
* X - the status column for the row
* AA - The commit date column for the row
* Reference - a worksheet containing reference values - the noted array pairs status values with either "Inactive" or "Active"
* Both date columns are short date data

The Issue
When the commit date = "today" NETWORKDAYS() returns "-2"

Thoughts?

[shg]

=NETWORKDAYS(TODAY(), TODAY()) returns 0 or 1, depending on whether TODAY() is a weekend day.

Suggest you watch the formula evaluate.

[nhmiller]

=NETWORKDAYS(TODAY(), TODAY()) returns 0 or 1, depending on whether TODAY() is a weekend day.

Suggest you watch the formula evaluate.

hmmm - am I missing something?
From http://office.microsoft.com/en-us/ex...005209190.aspx

NETWORKDAYS Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.

The unexpected result is that today today minus yesterday would return -2 instead of -1.

[shg]

Networkdays includes both bookend dates.

networkdays(today, tomorrow) returns 2 if both are weekdays.

[nhmiller]

Networkdays includes both bookend dates.

networkdays(today, tomorrow) returns 2 if both are weekdays.

OK - so as I think about this - it is simply a matter of each day being viewed as a full day - and not with any directional / relational context. So networkdays(today, yesterday) counts both as full days - and I get a difference of -2 because the function counts all of today - and all of yesterday - nothing less... I think it makes sense - although it makes this a bunch less useful for me in that it creates confusion in my less interested team members, anger in the ones really concerned about perception, and angst in the managers that look at this data...

Thanks much for taking the time to respond...

[shg]

Confusion, anger, and angst -- the apotheosis of office ethos achieved in a single function. Sublime.