Determining the number of specific days between two dates in Excel

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Ron Rosenfeld]

=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

 

[Daniel.M]

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.

 

[Guest]

Bob,

Many thanks. Works great!

js

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)

 

[Bob Phillips]

Hi Daniel,

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

Bob

 

[Guest]

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]

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)

 

[Guest]

Once again... Thank you.

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)