Count number of specific days between dates

[TheAntisocial]

Hi all.

I'm hoping you can help me with a formula to count the number of specific days between dates.

For example, i have two dates in two cells

A1 = 20/08/11
B1 = 20/09/11

I have a third column with a specific day

C1 = Monday

Is there a formula i can use to calculate how many "C1"s there were between "A1" and "B1"??

Any help is greatly appreciated

Thanks

 

[Jonmo1]

If C1 is an actual text string "Monday"

Try

=SUMPRODUCT(--(TEXT(ROW(INDEX(A:A,A1):INDEX(A:A,A2)),"dddd")=A3))

 

[TiaXL]

Hi all.

I'm hoping you can help me with a formula to count the number of specific days between dates.

For example, i have two dates in two cells

A1 = 20/08/11
B1 = 20/09/11

I have a third column with a specific day

C1 = Monday

Is there a formula i can use to calculate how many "C1"s there were between "A1" and "B1"??

Any help is greatly appreciated

Thanks

USE VBA

Sub test()
Dim dStart As Date
Dim dEnd As Date
Dim iDateCount As Integer
dStart = RANGE("A1").VALUE
dEnd = RANGE("B1").VALUE
 
Do Until dStart = dEnd
  dStart = dStart + 1
  If Weekday(dStart) = WEEKDAY(RANGE("C1")).VALUE Then
    iDateCount = 1
  End If
Loop
 
 RANGE("D1").VALUE = IDATECOUNT
End Sub

 

[TheAntisocial]

C1 is indeed a text string, and the formula works a treat!

Thanks for the help, very much appreciated