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 wrote in message
...
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"
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" wrote in message
...
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 wrote in message
...
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 wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 wrote in message
...
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. Im 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 wrote in message
...
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 wrote in message
...
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 wrote in message
...
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 wrote in message
...
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 wrote in message
...
I need a formula or process that allows you to determine the number of
specific days (Mondays, Tuesdays, etc) between two dates.