Determining the number of specific days between two dates in Excel

I need a formula or process that allows you to determine the number of
specific days (Mondays, Tuesdays, etc) between two dates.

Here is a formula from Daniel M

=SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))

A2, B2 are the 2 dates, C2 is the day of the week (1=Sun, 2=Mon, etc.)

It is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"jon s" <jon [email protected]> wrote in message
news:[email protected]...
> I need a formula or process that allows you to determine the number of
> specific days (Mondays, Tuesdays, etc) between two dates.

Use DAYS360 or NETWORKDAYS (use this one if you only want to calculate
business days).

"jon s" wrote:

> I need a formula or process that allows you to determine the number of
> specific days (Mondays, Tuesdays, etc) between two dates.

On Fri, 18 Mar 2005 23:27:18 -0000, "Bob Phillips"
<[email protected]> wrote:

>=SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))

Neat formula. But if A2 and B2 are "dates", why the TRUNC function? Or did it
come from a construct where times were included?


--ron

Hi Bob,

Brute force!? : That must be from my earlier days ;-))

The number of DOW (1= Sunday, 2 = Monday, ..., 7 = Saturday) between a start
date (A1) and an end date (A2) is :

=INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7)

Regards,

Daniel M.

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Here is a formula from Daniel M
>
> =SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))
>
> A2, B2 are the 2 dates, C2 is the day of the week (1=Sun, 2=Mon, etc.)
>
> It is an array formula, so commit with Ctrl-Shift-Enter.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "jon s" <jon [email protected]> wrote in message
> news:[email protected]...
> > I need a formula or process that allows you to determine the number of
> > specific days (Mondays, Tuesdays, etc) between two dates.
>
>

Bob,

Many thanks. Works great!

js

"Bob Phillips" wrote:

> Here is a formula from Daniel M
>
> =SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))
>
> A2, B2 are the 2 dates, C2 is the day of the week (1=Sun, 2=Mon, etc.)
>
> It is an array formula, so commit with Ctrl-Shift-Enter.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "jon s" <jon [email protected]> wrote in message
> news:[email protected]...
> > I need a formula or process that allows you to determine the number of
> > specific days (Mondays, Tuesdays, etc) between two dates.
>
>
>

Hi Daniel,

It is the one I have in my library, but checking Google, I see that you have
'moved on' :-)

Bob


"Daniel.M" <[email protected]> wrote in message
news:e6O4k%[email protected]...
> Hi Bob,
>
> Brute force!? : That must be from my earlier days ;-))
>
> The number of DOW (1= Sunday, 2 = Monday, ..., 7 = Saturday) between a
start
> date (A1) and an end date (A2) is :
>
> =INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7)
>
> Regards,
>
> Daniel M.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Here is a formula from Daniel M
> >
> > =SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))
> >
> > A2, B2 are the 2 dates, C2 is the day of the week (1=Sun, 2=Mon, etc.)
> >
> > It is an array formula, so commit with Ctrl-Shift-Enter.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "jon s" <jon [email protected]> wrote in message
> > news:[email protected]...
> > > I need a formula or process that allows you to determine the number of
> > > specific days (Mondays, Tuesdays, etc) between two dates.
> >
> >
>
>

do have a twist on the earlier questions and wondered if anyone knows how to
do it. I’m trying to do the following:

Within a date range (say 1/5/05 to 3/25/05) I would like to identify each
month and further find the number of Mondays, Tuesdays, and etc, within each
month.

Any thoughts? Thanks for the help,


"Bob Phillips" wrote:

> Here is a formula from Daniel M
>
> =SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))
>
> A2, B2 are the 2 dates, C2 is the day of the week (1=Sun, 2=Mon, etc.)
>
> It is an array formula, so commit with Ctrl-Shift-Enter.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "jon s" <jon [email protected]> wrote in message
> news:[email protected]...
> > I need a formula or process that allows you to determine the number of
> > specific days (Mondays, Tuesdays, etc) between two dates.
>
>
>

Assuming the dates are in A1 and B1, this will return the number of Mondays
in January

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=1),--(MONTH(ROW(INDIRECT(
A1&":"&B1)))=1))

The first = 1 refers to the weekday (1 through 7 for Mon through Sun), the
second refers to the month number.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon S" <Jon [email protected]> wrote in message
news:[email protected]...
> do have a twist on the earlier questions and wondered if anyone knows how
to
> do it. I'm trying to do the following:
>
> Within a date range (say 1/5/05 to 3/25/05) I would like to identify each
> month and further find the number of Mondays, Tuesdays, and etc, within
each
> month.
>
> Any thoughts? Thanks for the help,
>
>
> "Bob Phillips" wrote:
>
> > Here is a formula from Daniel M
> >
> > =SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))
> >
> > A2, B2 are the 2 dates, C2 is the day of the week (1=Sun, 2=Mon, etc.)
> >
> > It is an array formula, so commit with Ctrl-Shift-Enter.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "jon s" <jon [email protected]> wrote in message
> > news:[email protected]...
> > > I need a formula or process that allows you to determine the number of
> > > specific days (Mondays, Tuesdays, etc) between two dates.
> >
> >
> >

Once again... Thank you.

"Bob Phillips" wrote:

> Assuming the dates are in A1 and B1, this will return the number of Mondays
> in January
>
> =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=1),--(MONTH(ROW(INDIRECT(
> A1&":"&B1)))=1))
>
> The first = 1 refers to the weekday (1 through 7 for Mon through Sun), the
> second refers to the month number.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jon S" <Jon [email protected]> wrote in message
> news:[email protected]...
> > do have a twist on the earlier questions and wondered if anyone knows how
> to
> > do it. I'm trying to do the following:
> >
> > Within a date range (say 1/5/05 to 3/25/05) I would like to identify each
> > month and further find the number of Mondays, Tuesdays, and etc, within
> each
> > month.
> >
> > Any thoughts? Thanks for the help,
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Here is a formula from Daniel M
> > >
> > > =SUM(--(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2))
> > >
> > > A2, B2 are the 2 dates, C2 is the day of the week (1=Sun, 2=Mon, etc.)
> > >
> > > It is an array formula, so commit with Ctrl-Shift-Enter.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "jon s" <jon [email protected]> wrote in message
> > > news:[email protected]...
> > > > I need a formula or process that allows you to determine the number of
> > > > specific days (Mondays, Tuesdays, etc) between two dates.
> > >
> > >
> > >
>
>
>