Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the Number of Weekdays by Month
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the Number of Weekdays by Month
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 --------------- Jacob Skaria "DaveB" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the Number of Weekdays by Month
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: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the Number of Weekdays by Month
On Wed, 8 Jul 2009 05:04:01 -0700, DaveB
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging Month & weekdays | Excel Discussion (Misc queries) | |||
Extract Number of Weekdays (Mon-Fri) & Saturdays for a particular Month/Yr | Excel Worksheet Functions | |||
Count number of row for Particular month | Excel Worksheet Functions | |||
Weekdays of the month. | Excel Discussion (Misc queries) | |||
How do i count number of weekdays between two dates? | Excel Worksheet Functions |