Date Question?

[pinholsm]

I have a Spreadheet that has in one column the start date and in another column the end date then another coulmn with the frequency of occurrences

For Example: 9-Jul-07 start 20-aug-07 end 234 frequency

the frequency code is Monday is 1 Tuesday is 2 etc Sunday is 7

I need to know how many Tuesday Wednesdys and Thursday's are in that dat range

Any Ideas?

 

[morleyuk]

The following I got from "The Excel Logic Page
http://geocities.com/aaronblood" but should do the job.

Create the following user function in VB Editor:

Function Days(Date1 As Long, Date2 As Long, Weekday As Integer) As Long

Application.Volatile

If Date1 < Date2 Then
x = Date1
y = Date2
Else
x = Date2
y = Date1
End If

n = 0
If Weekday = 7 Then Weekday = 0

For daycnt = x To y
If daycnt Mod 7 = Weekday Then
n = n + 1
End If
Next

Days = n

End Function


Then user the following formula in the appropriate number of ceels for days of the week you need to count:

=days('start date cell1,'end date cell','day of week No')

This function works on Sunday = 1 etc

 

[Scott Huish]

Perhaps:

Put the days of the week in B1:B7

Formula in C1:
=SUMPRODUCT(--(WEEKDAY($A$1:$A$10000,2)=ROW(A1)),--($A$1:$A$10000<>""))
Copy down to C7

Book2
	A	B	C	D
1	7/9/2007	Monday	2
2	7/16/2007	Tuesday	0
3	7/15/2007	Wednesday	0
4	6/24/2007	Thursday	0
5	9/1/2007	Friday	0
6		Saturday	1
7		Sunday	2
Sheet1

 

[barry houdini]

Do you just want the total number of those days? Perhaps like this [edited]

Book1
	A	B	C	D	E
1	start	end	days	frequency
2	Mon 09-Jul-2007	Mon 20-Aug-2007	234	18
3	Mon 09-Jul-2007	Mon 20-Aug-2007	1	7
4	Mon 09-Jul-2007	Wed 11-Jul-2007	3	1
5	Sun 01-Jul-2007	Tue 31-Jul-2007	1234567	31
6			12	0
7
Sheet1

Formula in D2 copied down

=SUMPRODUCT(1-ISERR(FIND(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2),C2)))

 

[pinholsm]

WORKS AWESOME THANKS