# of Sundays in a year

L

lsmft

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?
 
X

xlbo

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"
 
N

Niek Otten

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

|
| 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?action=getinfo&userid=30678
| View this thread: http://www.excelforum.com/showthread.php?threadid=536328
|
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
J

JimMay

How do you get the # of Wednesdays?


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)
Click to expand...
 
B

Bob Phillips

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)
 
J

JimMay

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
 
B

Bob Phillips

If you haven't got a full date in D3, just a year, use

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE($D3,1,1)&":"&DATE($D3+1,1,0))))=1))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
N

Niek Otten

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

|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)
|
| | > 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
| >
| 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?action=getinfo&userid=30678
| > | View this thread:
| http://www.excelforum.com/showthread.php?threadid=536328
| > |
| >
| >
|
|
 
R

Roger Govier

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.
 
B

Bob Phillips

I get 52 for Monday

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE(YEAR(A1)+1,1
,0))))=2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Niek Otten said:
I should have stated more clearly, Year in A1 (like 2000 or 2006), not a
Click to expand...
date.

Doh! To much of reading what I expected.
One of your famous Sumproduct and -- solutions! Do you have them for
Click to expand...
breakfast as well? <g>

Well I do kinda find them useful, as long as you don't need hundreds of them
in a spreadsheet :)
 
D

Dana DeLouis

(52+(OR(WEEKDAY(DATE(YEAR(A1),1,1))=1,AND(MONTH(DATE(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})
 
R

Roger Govier

Bob
As I said, "it must be me doing something silly".
I had made the formula
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(A1),1,1)&":"&DATE(YEAR(A1)+1,1
,0))))=ROW()))
and copied down through rows 1 to 7 to give the answer for each day of
the week.
I hadn't locked $A$1 hence the variation in my result.
 
L

lsmft

Thank you for all of your help. Once again, the impossible seems t
become possible at this website
 
D

Dana DeLouis

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