| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate no. of Sundays in a month
Hi everybody,
Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
Try this:
With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
Hi Ron,
Thanx for the help. It was very helpful "Ron Coderre" wrote: Try this: With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
I'm glad I could help........and thanks for the feedback
*********** Regards, Ron XL2003, WinXP "Arup C" wrote: Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
What about if you wnat to know the no. of Sundays, but just from a single
cell, say A1=3/1/2007 Thks "Ron Coderre" wrote: Try this: With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
On Jun 13, 2:34*pm, Nacho wrote:
What about if you wnat to know the no. of Sundays, but just from a single cell, say A1=3/1/2007 The following allows A1 to be any date within the month. =SUMPRODUCT(--(WEEKDAY(EOMONTH(D9,-1)+ROW($A$1:INDIRECT("$A $"&DAY(EOMONTH(D9,0)))))=1)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
Here is a formula that, unlike joeu2004's offering, does not require the
Analysis ToolPak and is also not volatile... =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Nacho" wrote in message ... What about if you wnat to know the no. of Sundays, but just from a single cell, say A1=3/1/2007 Thks "Ron Coderre" wrote: Try this: With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
You are quite correct... it was left over from my testing, when I was trying
to get the individual sections to work correctly. Thanks for noticing that. Although the original formula works correctly (the double unary, the minus-minus signs, does nothing more than multiply by one), here is the corrected formula for the archives... =4+SUMPRODUCT((WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1 )-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Sandy Mann" wrote in message ... Rick, =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Being as you are multiplying in the SUMPRODUCT() surely you don't need the double unary? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... Here is a formula that, unlike joeu2004's offering, does not require the Analysis ToolPak and is also not volatile... =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Nacho" wrote in message ... What about if you wnat to know the no. of Sundays, but just from a single cell, say A1=3/1/2007 Thks "Ron Coderre" wrote: Try this: With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
Try this:
=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DOW)) Where DOW = Mon - 1 thru Sun - 7 -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... You are quite correct... it was left over from my testing, when I was trying to get the individual sections to work correctly. Thanks for noticing that. Although the original formula works correctly (the double unary, the minus-minus signs, does nothing more than multiply by one), here is the corrected formula for the archives... =4+SUMPRODUCT((WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1 )-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Sandy Mann" wrote in message ... Rick, =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Being as you are multiplying in the SUMPRODUCT() surely you don't need the double unary? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... Here is a formula that, unlike joeu2004's offering, does not require the Analysis ToolPak and is also not volatile... =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Nacho" wrote in message ... What about if you wnat to know the no. of Sundays, but just from a single cell, say A1=3/1/2007 Thks "Ron Coderre" wrote: Try this: With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
Very nice!! I'm still trying to work out the math behind why your formula
works; but, being a programmer (we tend to favor zero-based series), I would have said... Where DOW = Sun - 0 thru Sat -6 And, while I recognize you were giving a general solution, I would note that since the OP's question was for Sundays, your formula simplifies to this for that condition... =4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1))) Rick "T. Valko" wrote in message ... Try this: =4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DOW)) Where DOW = Mon - 1 thru Sun - 7 -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... You are quite correct... it was left over from my testing, when I was trying to get the individual sections to work correctly. Thanks for noticing that. Although the original formula works correctly (the double unary, the minus-minus signs, does nothing more than multiply by one), here is the corrected formula for the archives... =4+SUMPRODUCT((WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1 )-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Sandy Mann" wrote in message ... Rick, =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Being as you are multiplying in the SUMPRODUCT() surely you don't need the double unary? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... Here is a formula that, unlike joeu2004's offering, does not require the Analysis ToolPak and is also not volatile... =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Nacho" wrote in message ... What about if you wnat to know the no. of Sundays, but just from a single cell, say A1=3/1/2007 Thks "Ron Coderre" wrote: Try this: With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
Afternoon from RSA,
Adding on to this, how would you change the formula to not just be for Sundays but for all other days in the week. A1=Mon A2=Tues A3=Wed A4=Thur A5=Fri A6=Sat A7=Sun Thanks "Rick Rothstein (MVP - VB)" wrote: You are quite correct... it was left over from my testing, when I was trying to get the individual sections to work correctly. Thanks for noticing that. Although the original formula works correctly (the double unary, the minus-minus signs, does nothing more than multiply by one), here is the corrected formula for the archives... =4+SUMPRODUCT((WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1 )-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Sandy Mann" wrote in message ... Rick, =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Being as you are multiplying in the SUMPRODUCT() surely you don't need the double unary? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Rick Rothstein (MVP - VB)" wrote in message ... Here is a formula that, unlike joeu2004's offering, does not require the Analysis ToolPak and is also not volatile... =4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))28)) Rick "Nacho" wrote in message ... What about if you wnat to know the no. of Sundays, but just from a single cell, say A1=3/1/2007 Thks "Ron Coderre" wrote: Try this: With Dates in A1:A10 This formula counts the number of Saturdays in that range: =SUMPRODUCT(--(WEEKDAY(A1:A10)=7)) To count other days: 1=Sunday, 2=Monday,....7=Saturday Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Arup C" wrote in message ... Hi everybody, Can we calculate no. of sundays from a given range of dates formatted as "d mmm yy ddd" for example 1 Nov 07 Thu 2 Nov 07 Fri 3 Nov 07 Sat 4 Nov 07 Sun and so on.... |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
On Thu, 19 Jun 2008 06:22:02 -0700, Sunnyskies
wrote: Afternoon from RSA, Adding on to this, how would you change the formula to not just be for Sundays but for all other days in the week. A1=Mon A2=Tues A3=Wed A4=Thur A5=Fri A6=Sat A7=Sun Thanks To generalize, a formula giving the number of specific weekdays in the range from date to another (i.e. inclusive of the starting and ending dates) can be given by: =INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7) where A1: Start Date A2: End Date DOW: 1=Sunday; 2=Monday; etc --ron |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
Thanks Ron, works well. So well that I suggest you go home. Tell your Boss
Uncle Bob says so. Cheers "Ron Rosenfeld" wrote: On Thu, 19 Jun 2008 06:22:02 -0700, Sunnyskies wrote: Afternoon from RSA, Adding on to this, how would you change the formula to not just be for Sundays but for all other days in the week. A1=Mon A2=Tues A3=Wed A4=Thur A5=Fri A6=Sat A7=Sun Thanks To generalize, a formula giving the number of specific weekdays in the range from date to another (i.e. inclusive of the starting and ending dates) can be given by: =INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7) where A1: Start Date A2: End Date DOW: 1=Sunday; 2=Monday; etc --ron |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate no. of Sundays in a month
On Thu, 19 Jun 2008 08:04:05 -0700, Sunnyskies
wrote: Thanks Ron, works well. So well that I suggest you go home. Tell your Boss Uncle Bob says so. Cheers It's so nice to have cheery rejoinders like yours. It gratifies me that yours is the exception in these groups. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want to calculate Sundays between a specific date & today () | Excel Discussion (Misc queries) | |||
| Excel - list days of a month, excluding Sundays | Excel Discussion (Misc queries) | |||
| Formula to get 1st,2nd,3rd,4th,5th Sundays from month end date. | Excel Discussion (Misc queries) | |||
| calculate how many sundays between two dates in excel | Excel Discussion (Misc queries) | |||
| calculate how many sundays between two dates in excel | Excel Discussion (Misc queries) | |||
![Arup C[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
