# Counting number of Fri/Thur in a month

#### aamir

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

Number of days in a month
Code:
``=DAY(DATE(YEAR(E2),MONTH(E2)+1,0))``

Number of Thursdays
Code:
``=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(B:B,E2-DAY(E2)+1):INDEX(B:B,EOMONTH(E2,0))))=5))``

Number of Fridays
Code:
``=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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

Code:
``````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``````

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})

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})

Replies
4
Views
345
Replies
1
Views
452
Replies
2
Views
287
Replies
4
Views
376
Replies
1
Views
187

1,207,172
Messages
6,076,919
Members
446,241
Latest member
Nhacai888b

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back