Counting number of Fri/Thur in a month

[aamir]

i know how to find the days in a month by using the following formula:

Number of days in a month

=DAY(DATE(YEAR(E2),MONTH(E2)+1,0))

Number of Thursdays

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(B:B,E2-DAY(E2)+1):INDEX(B:B,EOMONTH(E2,0))))=5))

Number of Fridays

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(B:B,E2-DAY(E2)+1):INDEX(B:B,EOMONTH(E2,0))))=6))

My questions:

1. is thr any simple formula for above formaule?

2. I want to calculate,(total # of days in a month)- minus (sum of Fridays and Thursdays) in that month multiplied with 9 and this complete is added to number of (Thursdays multiplied by)* 5

 

[venkat1926]

the date(for e.g. 10/27/11) is in E2 and you want to find the number of thursdays and fridays in that month

try this macro do you get what you want? of course this is a macro and not a formula

Sub test()
Dim j As Long, k As Long, m As Long, ddate, r As Range
Set r = Range("e2")
ddate = Month(r) + 1 & "/" & 1 & "/" & Year(r) - 1
'MsgBox ddate
j = Day(CDate(ddate) - 1)
'MsgBox j
For k = 1 To j
If Weekday(DateSerial(Year(r), Month(r), k)) = 6 Or Weekday(DateSerial(Year(r), Month(r), k)) = 5 Then m = m + 1
Next k
MsgBox m
End Sub

 

[barry houdini]

2. I want to calculate,(total # of days in a month)- minus (sum of Fridays and Thursdays) in that month multiplied with 9 and this complete is added to number of (Thursdays multiplied by)* 5

So in effect every day in the month scores 9, except for Thursdays, which score 5 and Fridays which score zero? That would give a total of 227 for October 2011 as there are 4 Thursdays and 4 Fridays? For that total try this formula where E2 is any date

=SUM((DAY(E2-DAY(E2)+35)< WEEKDAY(E2-DAY(E2)-{0,1,2,3,4,5,6}))*{9,9,9,9,5,0,9})+200

or in Excel 2010 you can use this version

=SUM(NETWORKDAYS.INTL(E2-DAY(E2)+1,EOMONTH(E2,0),{6,"1110111","1111011"})*{9,5,0})

 

[aamir]

thanks it works... perfect!!!

In Excel 2010 you can use this version

=SUM(NETWORKDAYS.INTL(E2-DAY(E2)+1,EOMONTH(E2,0),{6,"1110111","1111011"})*{9,5,0})