Finding number of particular days in a period

[Guest]

I need to find the number of Thursdays in a period that occur on the 15th, 16th, 17th, 18th, 19,th 20th or 21st dates

For example, I need to write a formula that tells me say, how many occur between 16th July and 20th August 2004. The answer would be 1.

 

[Frank Kabel]

Hi
lets assume your starting state (e.g. 16th July) is in cell A1 and your
ending date in cell A2 (e.g. 20th August) then try the following
formula
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1 & ":" &
A2)))=5),--(DAY(ROW(INDIRECT(A1 & ":" &
A2)))>=15),--(DAY(ROW(INDIRECT(A1 & ":" & A2)))<=21))

 

[Daniel.M]

Also (for this specific case):

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1 & ":" &
A2)))=5),--(DAY(ROW(INDIRECT(A1 & ":" &
A2)))>=15),--(DAY(ROW(INDIRECT(A1 & ":" & A2)))<=21))

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=5)*
(ABS(DAY(ROW(INDIRECT(A1&":"&A2)))-18)<4))

Regards,

Daniel M.

 

[Guest]

BOGGEL!
If you have your start date and end date in adjacent cells (in same row) how
would that change this formula? And, do you replace the word WEEKDAY with
the actual name of the weekday (i.e., Thursday)? If I have my weekday(s)
listed in a different column (they are variables from row to row for which I
am trying to create a universal formula) on the same line (or in my case
several columns because I need to count up, say, Tuesdays & Thursdays) can I
use the cell ID rather than the name?

You have helped me before and I always appreciate the depth of your knowledge.

Thanks!