Counting days between dates

[Guest]

I am trying to calculate working days late or early between two dates using
=NETWORKDAYS(A2,B2)
If I have a due date of 24 Sept and I ship on 24 Sep then I am neither late
or early so I am on time i.e. 0 days - but my formula returns 1!
If I have a due date of 24 Sept and I ship on 23 Sep then I am shipping 1
day early i.e. –1 – but my formula returns –2
formula
due actual days early late should be
24-Sep 24-Sep 1 0
24-Sep 25-Sep 1 1
24-Sep 23-Sep -2 -1


What do I need to do to correct this?

Many thanks
M Suddes

 

[Alex Delamain]

Try this

=IF(A2=B2,0,IF(A2>B2,NETWORKDAYS(A2-1,B2),NETWORKDAYS(A2+1,B2)))

where A2 is Due date and B2 is Actual ship date

 

[Guest]

Thanks alex, this is close but still not quite right, it takes care of the
dates which are the same ie the ontime jobs = 0 days, but sill does not cater
for the early or lates!

Regards

m Suddes

 

[Alex Delamain]

It appears that Networkdays is only seeing the result of the formula
ie actual date 24/9 -due date 23/9 = 1). It then interprets a 1 a
Sunday so no working days have passed!

I tried setting up a series of dates as shown below
Due Date Actual DateActual DayNetworkdays
24-Sep 19-Sep Fri -5
24-Sep 20-Sep Sat -5
24-Sep 21-Sep Sun -4
24-Sep 22-Sep Mon -3
24-Sep 23-Sep Tue -2
24-Sep 24-Sep Wed 1
24-Sep 25-Sep Thu 1
24-Sep 26-Sep Fri 1
24-Sep 27-Sep Sat 2
24-Sep 28-Sep Sun 3
24-Sep 29-Sep Mon 4
24-Sep 30-Sep Tue 5
24-Sep 01-Oct Wed 6
24-Sep 02-Oct Thu 6
24-Sep 03-Oct Fri 6
24-Sep 04-Oct Sat 7
24-Sep 05-Oct Sun 8
24-Sep 06-Oct Mon 9
24-Sep 07-Oct Tue 10

The problem is that is is counting on blocks of 7 days regardless o
which days they actually are!.
Not sure of the solution at present but I am sure someone brighter tha
me will post one

 

[Alex Delamain]

Try this one then


=IF(B8>=A8,NETWORKDAYS(A8,B8)-1,NETWORKDAYS(A8,B8)+1

 

[Guest]

Alex many many thanks this works perfect
Cheers
Mark Suddes

 

[Guest]

Just wanted to add my 2 cents: this formula worked for me also, figuring
early and late shipments, where the NETWORKDAYS function kept adding an extra
day. Thank you much.

 

[Guest]

This formula worked for me also - how do you exclude certain days that are
holidays and listed in a different column. Thanks so much - I have been
struggling with NETWORKDAYS for some time now.

 

[Guest]

I'm not sure how to exclude holidays using this formula to count the number
of working days between two dates. I want to exclude 10 specific holidays
which would normally be considered work days.

Hausma

 

[Dave Peterson]

Put those 10 holidays in A1:A10 of a different sheet.

Then look at the help for =networkdays().

You'll see a sample formula like:

=NETWORKDAYS(A2,A3,A4:A6)

You'd point at that other sheet:
=NETWORKDAYS(A2,A3,sheet2!A1:A10)

 

[Guest]

Dave, thanks for the quick response. I'm still having trouble getting the
formula correct. I'm am trying to determine how many on time, early and late
shipments we have. This formula seemed to give me the information that I
needed but then I realized that it was treating holidays as workdays also.
So I need to exclude these 10 days. I have entered them on a different sheet
and pointed to this sheet but I must be entering something incorrectly
because I get a paraenthesis error or #value

=IF(B8>=A8,NETWORKDAYS(A8,B8)-1,NETWORKDAYS(A8,B8)+1,NETWORKDAYS(A8,B8,HOLIDAYS!A1:A10)
Am I doing this correctly?

Hausman

 

[Dave Peterson]

I would have guessed that you wanted the holidays!a1:a10 in each portion:

=IF(B8>=A8,NETWORKDAYS(A8,B8,holidays!a1:a10)-1,
NETWORKDAYS(A8,B8,holidays!a1:a10)+1)
(all one cell)

Dave, thanks for the quick response. I'm still having trouble getting the
formula correct. I'm am trying to determine how many on time, early and late
shipments we have. This formula seemed to give me the information that I
needed but then I realized that it was treating holidays as workdays also.
So I need to exclude these 10 days. I have entered them on a different sheet
and pointed to this sheet but I must be entering something incorrectly
because I get a paraenthesis error or #value

=IF(B8>=A8,NETWORKDAYS(A8,B8)-1,NETWORKDAYS(A8,B8)+1,NETWORKDAYS(A8,B8,HOLIDAYS!A1:A10)
Am I doing this correctly?

Hausman