Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Barbara
 
Posts: n/a
Default Calculate number of days in a column of dates

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
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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



  #3   Report Post  
Barbara
 
Posts: n/a
Default

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" wrote:

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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




  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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






  #5   Report Post  
Barbara
 
Posts: n/a
Default

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" wrote:

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.

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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








  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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)

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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.

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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








  #7   Report Post  
Barbara
 
Posts: n/a
Default

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?

"Bob Phillips" wrote:

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)

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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.

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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









  #8   Report Post  
bj
 
Posts: n/a
Default

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" wrote:

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)

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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.

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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









  #9   Report Post  
Bob Phillips
 
Posts: n/a
Default

Hi again Barbara,

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

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

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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?

"Bob Phillips" wrote:

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)

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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.

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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" wrote:

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?

--
HTH

Bob Phillips

"Barbara" wrote in message
...
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











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need a formula to sum column b if column a is between two dates Pam Excel Discussion (Misc queries) 2 April 18th 05 06:18 PM
Count cells in a column that contain dates Cachod1 New Users to Excel 1 March 29th 05 08:56 PM
How do you calculate number of workdays from dates entered? tfleck Excel Worksheet Functions 1 March 25th 05 10:17 PM
How do you calculate number of workdays from dates entered? [email protected] Excel Worksheet Functions 0 March 25th 05 08:55 PM
Calculate the number of workdays between 2 dates Tegid77 Excel Worksheet Functions 1 November 4th 04 08:27 PM


All times are GMT +1. The time now is 11:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"