Thread: Date Calculation using NETWORKDAYS

Hi - it's me again.

I'm producing a spreadsheet showing jobs outstanding on a helpdesk system. I have a title at the top reading 'Oustanding jobs as at' and have isolated the next cell which contains the function =TODAY(). In the body of the sheet is a columnar list of outstanding jobs with one column showing the date the job was submitted. The next column is headed 'Days Outstanding' and contains the formula = NETWORKDAYS(E1,D5) (where E1 is the isolated cell in the title and D5 is the first cell containing an outstanding job submission date). All relevant cells are formatted to 'Date' in the 19/3/2010 format.

This is returning a number of days outstanding for each job, which is what I expected. The problem is that a job logged today is shown as being one day outstanding, and a job logged yesterday as two days. Obviously anything logged today should be returning a value of zero, and all my figures are out by one day.

Two questions:

1) Why is this happening? Excel processes dates, however they are shown, as numbers. A particular date must represent a particular number, and when you subtract a number from itself the answer is inevitably zero. I really don't understand this at all.

2) How can I fix it? I tried the obvious by appending -1 to the end of the function but it threw a hissy fit and decided to return a date of 1/1/1900, which really wasn't helpful!

Many thanks in advance for hopefully solving a numpty's second query in two days. My first was resolved yesterday, only a couple of working hours after I posted it, which |I think is awesome!

NETWORKDAYS treats the days as being inclusive - ie if you started work today and left work today you worked 1 day not 0 days.

To make exclusive you should as you have already outlined - subtract 1 from the result - ie NETWORKDAYS(E$1,D5)-1

Ensure the cell is formatted as General ... 1 in date form would show as 01/01/1900 whereas 0 would show as 00/01/1900