Counting number of Fri/Thur in a month

aamir

Board Regular
Joined
Feb 17, 2010
Messages
116
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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})
 
Upvote 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})
 
Upvote 0

Forum statistics

Threads
1,224,213
Messages
6,177,167
Members
452,763
Latest member
WH12TTY

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top