Calculate number of days in a column of dates

[Guest]

Hi,

I have a sheet with a column "Day" where the production date is introduced
for each mouvement (5-1-04; 6-1-04, 6,2,04...) along the year (those days can
be repeated according to the production).
I need to calculate how many days of production there is by month and by year.
Can somebody help me????
Tks.
Barbara

 

[Bob Phillips]

Hi Barbara,

A bit more detail please.

Does a cell in that column contain many dates or just 1?

If the former, give an example of how you would manually calculate the
production days with say 3 dates in the cell, and how the dates are
differentiated.

If the latter, what signifies a start date and what an end date?

Do start and end dates get included?

 

[Guest]

Hi Bob, thaks for your reply. I' ll be more specific giving you part of my
sheet.

05-01-2004 Vigaria
05-01-2004 Vigaria
06-01-2004 Vigaria
06-01-2004 Vigaria
06-01-2004 Vigaria
06-01-2004 Vigaria
07-01-2004 Vigaria
07-01-2004 Vigaria
08-01-2004 Vigaria
08-01-2004 Vigaria
08-01-2004 Vigaria
08-01-2004 Vigaria
09-01-2004 Cabouca
09-01-2004 Vigaria
09-01-2004 Vigaria
12-01-2004 Cabouca

Here, we have 6 days of production. Hope that you can help me.

 

[Bob Phillips]

Is this simply the highest date less the lowest? If so, then

=NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000))

NETWORKDAYS is part of the analysis toolpak add-in, so that must be
installed.

 

[Guest]

Hi again,

I still have a problem!!!
in this list, with your formula, it returns 16 days. But the right answer is
14. Why? How can I have the right answer?
02-Dez
02-Dez
02-Dez
02-Dez
02-Dez
02-Dez
02-Dez
02-Dez
03-Dez
07-Dez
07-Dez
09-Dez
10-Dez
10-Dez
13-Dez
13-Dez
13-Dez
14-Dez
14-Dez
15-Dez
15-Dez
15-Dez
15-Dez
16-Dez
16-Dez
17-Dez
17-Dez
17-Dez
20-Dez
20-Dez
21-Dez
21-Dez
21-Dez
21-Dez
21-Dez
22-Dez
22-Dez
23-Dez
23-Dez

 

[Bob Phillips]

Barbara,

Is this because NETWORKDAYS is including both the start and end dates as
part of the difference, and you just want the days between? If so, I was
going to say just subtract 2 from it, but just in case the max or Min dates
are weekend days then use

=NETWORKDAYS(MIN(A1:A1000),MAX(A1:A1000))-(WEEKDAY(MIN(A1:A1000),2)<6)-(WEEK
DAY(MAX(A1:A1000),2)<6)

 

[Guest]

Bob,
The problem here is because there are no records for 6-dec and 8-dec which
are week days.

Is there any other way to calculate the number of days introduced in my sheet?

 

[Guest]

another thing which can happen is if there are days with no production which
is not a holiday or week end, the network days will give a high answer

something which might work is
=SUMPRODUCT(--(A1:A1000<>A2:A1001),--(A1:A1000<>""))
if all of the dates are in order.

 

[Bob Phillips]

Hi again Barbara,

So you just want to count the unique dates, including Sat and Sun?

=SUMPRODUCT((A1:A1000<>"")/COUNTIF(A1:A1000,A1:A1000&""))