Finding Number of Particular Dayns Problem 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.

Answer: 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))

Thankyou to those who answered this; your formula works, and it gives the right answer. However, this formula is activated from cells that get dates from a calendar. Whenever a date in the calendar is chosen, it gives a REF# answer, not a numerical answer as it should; but whenever you type the date in the cell, it works! Help - what do I need to change to get this formula to work from the calendar?

 

[Nimit Mehta]

What is the format of your date? Use a new cell B2 with
=A2 where A2 is the cell you get dates automatically by
clicking the date on the calender. Whenever you will click
the date on the calender, A2 will display the DATE in
maybe formulas and links and not values. So try doing =A2
in B2 and use B2 in your formula.
-Nimit

-----Original Message-----
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.

Answer: 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))

Thankyou to those who answered this; your formula works,

and it gives the right answer. However, this formula is
activated from cells that get dates from a calendar.
Whenever a date in the calendar is chosen, it gives a REF#
answer, not a numerical answer as it should; but whenever
you type the date in the cell, it works! Help - what do I
need to change to get this formula to work from the
calendar?

 

[Daniel.M]

Les,

Do a test if there are enough numeric entries.

=IF(COUNT(A1:A2)=2, SUMPRODUCT(...),"")

Regards,

Daniel M.

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.

Answer: 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))

Thankyou to those who answered this; your formula works, and it gives the

right answer. However, this formula is activated from cells that get dates from
a calendar. Whenever a date in the calendar is chosen, it gives a REF# answer,
not a numerical answer as it should; but whenever you type the date in the
cell, it works! Help - what do I need to change to get this formula to work
from the calendar?

 

[Frank Kabel]

Hi
this sounds like the calendar stores the value as 'Text' and not as
date. After re-entering the value Excel recognizes it as date 8numeric
value) and everything works fine.
Question: what calendar control are you using? And also check the
format of the cell