| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
# of Sundays in a year
I've noticed that there are 53 Sundays in this year 2006. My question: Is there a way for Excel to determine how many Sundays or any other day of the week that there is in a year? Also: Can Excel tell how often there will be 53 Sundays in a year? -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=536328 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
The simple answer is that only the 1st weekday of the year will have 53
occurances in that year - all others will have 52, except on LEap Years where the 1st 2 days will be repeated 53 times to find the weekday for the 1st of Jan, simply enter the date and format it as dddd =if(text(A1,"dddd") = "Sunday", "There will be 53 Sundays this year","There will be 52 Sundays this year") where the 1st of Jan test is in A1 -- Rgds, Geoff "A crash reduces Your expensive computer To a simple stone" "lsmft" wrote: I've noticed that there are 53 Sundays in this year 2006. My question: Is there a way for Excel to determine how many Sundays or any other day of the week that there is in a year? Also: Can Excel tell how often there will be 53 Sundays in a year? -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=536328 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
Nice approach, couldn't resist completing it :-)
="There will be "&(52+(OR(WEEKDAY(DATE(YEAR(A1),1,1))=1,AND(MONTH( DATE(YEAR(A1),2,29))=2,WEE KDAY(DATE(YEAR(A1),1,2))=1))))&" Sundays this year" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "xlbo" wrote in message ... The simple answer is that only the 1st weekday of the year will have 53 occurances in that year - all others will have 52, except on LEap Years where the 1st 2 days will be repeated 53 times to find the weekday for the 1st of Jan, simply enter the date and format it as dddd =if(text(A1,"dddd") = "Sunday", "There will be 53 Sundays this year","There will be 52 Sundays this year") where the 1st of Jan test is in A1 -- Rgds, Geoff "A crash reduces Your expensive computer To a simple stone" "lsmft" wrote: I've noticed that there are 53 Sundays in this year 2006. My question: Is there a way for Excel to determine how many Sundays or any other day of the week that there is in a year? Also: Can Excel tell how often there will be 53 Sundays in a year? -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=536328 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
(52+(OR(WEEKDAY(DATE(YEAR(A1),1,1))=1,AND(MONTH(DA TE(YEAR(A1),2,29))=2,WEEKDAY(DATE(YEAR(A1),1,2))=1 ))))
Just another option along this same line might be this array formula: =52+OR(WEEKDAY(DATE(A1,1,1))*MONTH(DATE(A1,2,29))= {2,3,14}) -- Dana DeLouis Windows XP, Office 2003 "Bob Phillips" wrote in message ... Nice approach, couldn't resist completing it :-) ="There will be "&(52+(OR(WEEKDAY(DATE(YEAR(A1),1,1))=1,AND(MONTH( DATE(YEAR(A1),2,29))=2,WEE KDAY(DATE(YEAR(A1),1,2))=1))))&" Sundays this year" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "xlbo" wrote in message ... The simple answer is that only the 1st weekday of the year will have 53 occurances in that year - all others will have 52, except on LEap Years where the 1st 2 days will be repeated 53 times to find the weekday for the 1st of Jan, simply enter the date and format it as dddd =if(text(A1,"dddd") = "Sunday", "There will be 53 Sundays this year","There will be 52 Sundays this year") where the 1st of Jan test is in A1 -- Rgds, Geoff "A crash reduces Your expensive computer To a simple stone" "lsmft" wrote: I've noticed that there are 53 Sundays in this year 2006. My question: Is there a way for Excel to determine how many Sundays or any other day of the week that there is in a year? Also: Can Excel tell how often there will be 53 Sundays in a year? -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=536328 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
Thank you for all of your help. Once again, the impossible seems to become possible at this website. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=536328 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
A more general method to generate the 14 unique calendars might be something
like this: =2*WEEKDAY(DATE(A2,1,1))+MONTH(DATE(A2,2,29)) (numbered: 4,5,6,...17) Calendars that have 53 Sundays are {4,5,16} =52+OR(2*WEEKDAY(DATE(A2,1,1))+MONTH(DATE(A2,2,29) )={4,5,16}) Calendars that have 53 Wednesdays are {8,10,11} =52+OR(2*WEEKDAY(DATE(A2,1,1))+MONTH(DATE(A2,2,29) )={8,10,11}) Monday: {4,6,7} Tuesday: {6,8,9} Thursday: {10,12,13} Friday:{12,14,15} Saturday: {14,16,17} -- HTH. :) Dana DeLouis Windows XP, Office 2003 "lsmft" wrote in message ... Thank you for all of your help. Once again, the impossible seems to become possible at this website. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=536328 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
I used a formula from Chip Person's site, http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear
With the year in A1: =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1,1,1)))))=A1,53,52) This formula works for leap years as well. -- Kind regards, Niek Otten "lsmft" wrote in message ... | | I've noticed that there are 53 Sundays in this year 2006. | My question: | Is there a way for Excel to determine how many Sundays or any other day | of the week that there is in a year? | Also: | Can Excel tell how often there will be 53 Sundays in a year? | | | -- | lsmft | ------------------------------------------------------------------------ | lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 | View this thread: http://www.excelforum.com/showthread...hreadid=536328 | |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
I can't get that to work yet Niek, but here is another one
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE( YEAR(A1)+1,1 ,0))))=1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... I used a formula from Chip Person's site, http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear With the year in A1: =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1, 1,1)))))=A1,53,52) This formula works for leap years as well. -- Kind regards, Niek Otten "lsmft" wrote in message ... | | I've noticed that there are 53 Sundays in this year 2006. | My question: | Is there a way for Excel to determine how many Sundays or any other day | of the week that there is in a year? | Also: | Can Excel tell how often there will be 53 Sundays in a year? | | | -- | lsmft | ------------------------------------------------------------------------ | lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 | View this thread: http://www.excelforum.com/showthread...hreadid=536328 | |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
Thanks Bob;
How would you change the given formula to get say the Wednesdays? TIA, "Bob Phillips" wrote in message : I can't get that to work yet Niek, but here is another one =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE( YEAR(A1)+1,1 ,0))))=1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... I used a formula from Chip Person's site, http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear With the year in A1: =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1, 1,1)))))=A1,53,52) This formula works for leap years as well. -- Kind regards, Niek Otten "lsmft" wrote in message ... | | I've noticed that there are 53 Sundays in this year 2006. | My question: | Is there a way for Excel to determine how many Sundays or any other day | of the week that there is in a year? | Also: | Can Excel tell how often there will be 53 Sundays in a year? | | | -- | lsmft | ------------------------------------------------------------------------ | lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 | View this thread: http://www.excelforum.com/showthread...hreadid=536328 | |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
Jim,
The =1 at the end is the Sunday check, so Wednesday would be =4. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JimMay" wrote in message news:rTI3g.11261$fG3.7044@dukeread09... Thanks Bob; How would you change the given formula to get say the Wednesdays? TIA, "Bob Phillips" wrote in message : I can't get that to work yet Niek, but here is another one =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE( YEAR(A1)+1,1 ,0))))=1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... I used a formula from Chip Person's site, http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear With the year in A1: =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1, 1,1)))))=A1,53,52) This formula works for leap years as well. -- Kind regards, Niek Otten "lsmft" wrote in message ... | | I've noticed that there are 53 Sundays in this year 2006. | My question: | Is there a way for Excel to determine how many Sundays or any other day | of the week that there is in a year? | Also: | Can Excel tell how often there will be 53 Sundays in a year? | | | -- | lsmft | ------------------------------------------------------------------------ | lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 | View this thread: http://www.excelforum.com/showthread...hreadid=536328 | |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
D3 is my input cell for year to be tested..
In F3 I entered: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR($D3),1,1)&":"&DATE (YEAR($D3)+1,1,0))))=1)) And copied across to L3, changing each cell's (only) last numeric to: G3 2 H3 3 I3 4 J3 5 K3 6 L3 7 As I change D2 - from 2000, to 2001, to 2002 etc All cells F3:L3 remain UNCHANGED - Doesn't seem right Sunday registers as 53 for all years... hummmmmm "Bob Phillips" wrote in message : Jim, The =1 at the end is the Sunday check, so Wednesday would be =4. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JimMay" wrote in message news:rTI3g.11261$fG3.7044@dukeread09... Thanks Bob; How would you change the given formula to get say the Wednesdays? TIA, "Bob Phillips" wrote in message : I can't get that to work yet Niek, but here is another one =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE( YEAR(A1)+1,1 ,0))))=1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... I used a formula from Chip Person's site, http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear With the year in A1: =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1, 1,1)))))=A1,53,52) This formula works for leap years as well. -- Kind regards, Niek Otten "lsmft" wrote in message ... | | I've noticed that there are 53 Sundays in this year 2006. | My question: | Is there a way for Excel to determine how many Sundays or any other day | of the week that there is in a year? | Also: | Can Excel tell how often there will be 53 Sundays in a year? | | | -- | lsmft | ------------------------------------------------------------------------ | lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 | View this thread: http://www.excelforum.com/showthread...hreadid=536328 | |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
Hi Bob
I'm probably doing something very silly, but I get an answer of 53 for both Sunday and Monday with this formula, and 52 for all other days. -- Regards Roger Govier "Bob Phillips" wrote in message ... Jim, The =1 at the end is the Sunday check, so Wednesday would be =4. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "JimMay" wrote in message news:rTI3g.11261$fG3.7044@dukeread09... Thanks Bob; How would you change the given formula to get say the Wednesdays? TIA, "Bob Phillips" wrote in message : I can't get that to work yet Niek, but here is another one =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE( YEAR(A1)+1,1 ,0))))=1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... I used a formula from Chip Person's site, http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear With the year in A1: =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1, 1,1)))))=A1,53,52) This formula works for leap years as well. -- Kind regards, Niek Otten "lsmft" wrote in message ... | | I've noticed that there are 53 Sundays in this year 2006. | My question: | Is there a way for Excel to determine how many Sundays or any other day | of the week that there is in a year? | Also: | Can Excel tell how often there will be 53 Sundays in a year? | | | -- | lsmft | ------------------------------------------------------------------------ | lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 | View this thread: http://www.excelforum.com/showthread...hreadid=536328 | |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
How do you get the # of Wednesdays?
"Bob Phillips" wrote in message : I can't get that to work yet Niek, but here is another one =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE( YEAR(A1)+1,1 ,0))))=1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Niek Otten" wrote in message ... I used a formula from Chip Person's site, http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear With the year in A1: =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1, 1,1)))))=A1,53,52) This formula works for leap years as well. -- Kind regards, Niek Otten "lsmft" wrote in message ... | | I've noticed that there are 53 Sundays in this year 2006. | My question: | Is there a way for Excel to determine how many Sundays or any other day | of the week that there is in a year? | Also: | Can Excel tell how often there will be 53 Sundays in a year? | | | -- | lsmft | ------------------------------------------------------------------------ | lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 | View this thread: http://www.excelforum.com/showthread...hreadid=536328 | |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
I should have stated more clearly, Year in A1 (like 2000 or 2006), not a date.
One of your famous Sumproduct and -- solutions! Do you have them for breakfast as well? <g -- Kind regards, Niek Otten "Bob Phillips" wrote in message ... |I can't get that to work yet Niek, but here is another one | | =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE( YEAR(A1)+1,1 | ,0))))=1)) | | -- | HTH | | Bob Phillips | | (remove nothere from email address if mailing direct) | | "Niek Otten" wrote in message | ... | I used a formula from Chip Person's site, | http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear | | With the year in A1: | | | =IF(YEAR(DATE(A1,1,1+((53-(1=WEEKDAY(DATE(A1,1,1))))*7)+(1-WEEKDAY(DATE(A1, | 1,1)))))=A1,53,52) | | This formula works for leap years as well. | | -- | Kind regards, | | Niek Otten | | "lsmft" wrote in | message | ... | | | | I've noticed that there are 53 Sundays in this year 2006. | | My question: | | Is there a way for Excel to determine how many Sundays or any other day | | of the week that there is in a year? | | Also: | | Can Excel tell how often there will be 53 Sundays in a year? | | | | | | -- | | lsmft | | ------------------------------------------------------------------------ | | lsmft's Profile: | http://www.excelforum.com/member.php...o&userid=30678 | | View this thread: | http://www.excelforum.com/showthread...hreadid=536328 | | | | | | |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
"Niek Otten" wrote in message ... I should have stated more clearly, Year in A1 (like 2000 or 2006), not a date. Doh! To much of reading what I expected. One of your famous Sumproduct and -- solutions! Do you have them for breakfast as well? <g Well I do kinda find them useful, as long as you don't need hundreds of them in a spreadsheet :-) |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
# of Sundays in a year
Hello,
There are many solutions to this. Instead of sumproducts or array formulas I would use: http://www.sulprobil.com/html/date_formulas.html (Thanks to Daniel M. again!) Regards, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get Excel to automatically calculate salaries actually received in financial year? | Excel Discussion (Misc queries) | |||
| Differentiating data by year into columns | Excel Worksheet Functions | |||
| Fiscal Year Calculation | Excel Worksheet Functions | |||
| years change to current year | Excel Worksheet Functions | |||
| Ho to Delete "Ghost" Pivot Tables | Excel Discussion (Misc queries) | |||
Hybrid Mode
