Calculate number of days in a column of dates

Bob Phillips · 05-25-2005, 09:22 AM

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" <Barbara@discussions.microsoft.com> wrote in message
news:F491CDDF-078B-4A5E-8CC1-8DC084834ABF@microsoft.com...
> 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" <Barbara@discussions.microsoft.com> wrote in message
> > news:911297EC-A3EC-4D86-BF2F-BF3796158612@microsoft.com...
> > > 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" <Barbara@discussions.microsoft.com> wrote in message
> > > > news:B90E6D02-332A-4750-AAF8-53438B0F58A4@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

Barbara · 05-25-2005, 09:42 AM

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" <Barbara@discussions.microsoft.com> wrote in message
> news:F491CDDF-078B-4A5E-8CC1-8DC084834ABF@microsoft.com...
> > 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" <Barbara@discussions.microsoft.com> wrote in message
> > > news:911297EC-A3EC-4D86-BF2F-BF3796158612@microsoft.com...
> > > > 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" <Barbara@discussions.microsoft.com> wrote in message
> > > > > news:B90E6D02-332A-4750-AAF8-53438B0F58A4@microsoft.com...
> > > > > > 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 · 05-25-2005, 09:55 AM

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" <Barbara@discussions.microsoft.com> wrote in message
news:BD4B73FD-3F31-4BF7-926F-8738D1830AAD@microsoft.com...
> 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" <Barbara@discussions.microsoft.com> wrote in message
> > news:F491CDDF-078B-4A5E-8CC1-8DC084834ABF@microsoft.com...
> > > 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" <Barbara@discussions.microsoft.com> wrote in message
> > > > news:911297EC-A3EC-4D86-BF2F-BF3796158612@microsoft.com...
> > > > > 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" <Barbara@discussions.microsoft.com> wrote in message
> > > > > > news:B90E6D02-332A-4750-AAF8-53438B0F58A4@microsoft.com...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

bj · 05-25-2005, 09:49 AM

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" <Barbara@discussions.microsoft.com> wrote in message
> news:F491CDDF-078B-4A5E-8CC1-8DC084834ABF@microsoft.com...
> > 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" <Barbara@discussions.microsoft.com> wrote in message
> > > news:911297EC-A3EC-4D86-BF2F-BF3796158612@microsoft.com...
> > > > 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" <Barbara@discussions.microsoft.com> wrote in message
> > > > > news:B90E6D02-332A-4750-AAF8-53438B0F58A4@microsoft.com...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>