Count the Number of Weekdays by Month

[DaveB]

I have a the following matrix that I want to populate with the number of
Saturdays, Sundays, Mondays, etc. by month, which is based off of a daily
Fiscal Year (i.e. FY 2010) calendar that begins with October 1, 2009 (40087)
and ends with September 30, 2010 (40451) down column A of a tab titled "FY
Calendar" When I change the Fiscal Year to 2011, 2012 I want the matrix
repopulated with the corrrect count of each weekday. I already have the
calendar built which displays each date down column B, the month in column C,
the day of the week in column D and the Weekday (1-7) in column E. If there
is an easier way to do all this I would appreciate the help.

A B C D E F
1 Saturday Sunday Monday Tuesday Wednesday etc...
2 October 5 4 4
3 November
4 December
5 January
6 February
7 March
8 April
9 May
10 June
11 July
12 August
13 September

 

[Jacob Skaria]

You will have to probably adjust the below formula to suit

Try the below with a date in A1 (excel date format)
and B1 with the weekday (numerics 1 to 7)
The result would be the number of weekdays for the month...

(all in one line)
=INT(((DATE(YEAR(A1),MONTH(A1)+1,1)-1)-MOD((DATE(YEAR(A1),MONTH(A1)+1,1)-1)-B1,7)-DATE(YEAR(A1),MONTH(A1),1)+7)/7)

If this post helps click Yes

 

[Stefi]

If Fiscal year is in A1 and you set up a helper column, say I filled with
=ROW() from I1 to I31, then enter in B2

=SUMPRODUCT(--(WEEKDAY(DATE($A$1,ROW()+IF(ROW()<=4,8,-4),$I$1:INDIRECT("$I$"&DAY(DATE($A$1,ROW()+IF(ROW()<=4,8,-4)+1,0)))))=COLUMN()+IF(COLUMN()<=2,5,-2)))

and fill it to the right and down!

Regards,
Stefi

„DaveB†ezt írta:

 

[Ron Rosenfeld]

I have a the following matrix that I want to populate with the number of
Saturdays, Sundays, Mondays, etc. by month, which is based off of a daily
Fiscal Year (i.e. FY 2010) calendar that begins with October 1, 2009 (40087)
and ends with September 30, 2010 (40451) down column A of a tab titled "FY
Calendar" When I change the Fiscal Year to 2011, 2012 I want the matrix
repopulated with the corrrect count of each weekday. I already have the
calendar built which displays each date down column B, the month in column C,
the day of the week in column D and the Weekday (1-7) in column E. If there
is an easier way to do all this I would appreciate the help.

A B C D E F
1 Saturday Sunday Monday Tuesday Wednesday etc...
2 October 5 4 4
3 November
4 December
5 January
6 February
7 March
8 April
9 May
10 June
11 July
12 August
13 September

Assuming the values in A2:A13 are Excel dates in the desired month and merely
formatted to show only the month name, then:

B2:
=4+(DAY($A2-DAY($A2)+35)<WEEKDAY($A2-DAY($A2)-MATCH(B$1,
{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0)))

Fill down to B13. Then select B2:B13 and fill right to column H.
--ron