Date Calculation using NETWORKDAYS

**Moondog** · 03-19-2010, 06:53 AM

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!

**DonkeyOte** · 03-19-2010, 07:04 AM

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

**Moondog** · 03-19-2010, 12:38 PM

Why is it that I cane my brain looking for the solution, and yet when someone gives it to you it seems so obvious?

Thank you, DonkeyOte. It seems I was doing everything right except formatting the cells to 'General'! It appears that the cells were autoformatting themselves to 'Date', probably because of the two date formats contained in the function.

**cbrf23** · 02-06-2015, 12:24 PM

Old thread, but I found it when searching and there is no acceptable answer yet so I wanted to answer it as it might help someone else in the future.

The problem with just subtracting 1 from the result is that it assumes your start date is a workday; the result will be wrong if your start date is a weekend or holiday.
What you need is to subtract a day if your start date is a workday, but subtract zero days if it's not a workday.

We can check if a date is a workday or not using networkdays with the same start and end date.

Please Login or Register  to view this content.

When A1 is a workday this will return 1. When A1 is a weekend or holiday this will return 0.
So knowing that we can calculate the non-inclusive number of days by subtracting the result of that check from our original networkdays result.

Please Login or Register  to view this content.

**daddylonglegs** · 02-06-2015, 12:33 PM

And for completeness......

If A2 is an earlier date than A1 NETWORKDAYS can return a negative number, in which case rather subtracting 1 you would need to add 1 - to take care of that you can use this version:

=NETWORKDAYS(A1,A2,holidays)-SIGN(NETWORKDAYS(A1,A1,holidays))