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

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

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" <[email protected]> wrote in message
news:[email protected]...
|
| 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
|

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in
message
> news:[email protected]...
> |
> | 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
> |
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

Thanks Bob;
How would you change the given formula to get say the Wednesdays?
TIA,

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]:

> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in
> message
> > news:[email protected]...
> > |
> > | 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
> > |
> >
> >

How do you get the # of Wednesdays?


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]:

> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in
> message
> > news:[email protected]...
> > |
> > | 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
> > |
> >
> >

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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]:
>
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in
> > message
> > > news:[email protected]...
> > > |
> > > | 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
> > > |
> > >
> > >
>

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" <[email protected]> wrote in message
news:#[email protected]:

> 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" <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]:
> >
> > > 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" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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" <[email protected]> wrote in
> > > message
> > > > news:[email protected]...
> > > > |
> > > > | 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
> > > > |
> > > >
> > > >
> >

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)

"JimMay" <[email protected]> wrote in message
news:B7J3g.11263$fG3.7385@dukeread09...
> 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" <[email protected]> wrote in message
> news:#[email protected]:
>
> > 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" <[email protected]> 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" <[email protected]> wrote in message
> > > news:[email protected]:
> > >
> > > > 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" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > 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" <[email protected]> wrote
in
> > > > message
> > > > > news:[email protected]...
> > > > > |
> > > > > | 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
> > > > > |
> > > > >
> > > > >
> > >
>

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" <[email protected]> wrote in message news:[email protected]...
|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" <[email protected]> wrote in message
| news:%[email protected]...
| > 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" <[email protected]> wrote in
| message
| > news:[email protected]...
| > |
| > | 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
| > |
| >
| >
|
|

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]:
>>
>> > 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" <[email protected]> wrote in message
>> > news:%[email protected]...
>> > > 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" <[email protected]>
>> > > wrote in
>> > message
>> > > news:[email protected]...
>> > > |
>> > > | 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
>> > > |
>> > >
>> > >
>>
>
>

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)

"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> 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" <[email protected]> wrote in message
> >> news:[email protected]:
> >>
> >> > 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" <[email protected]> wrote in message
> >> > news:%[email protected]...
> >> > > 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" <[email protected]>
> >> > > wrote in
> >> > message
> >> > > news:[email protected]...
> >> > > |
> >> > > | 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
> >> > > |
> >> > >
> >> > >
> >>
> >
> >
>
>

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> 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 :-)

> (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})
--
Dana DeLouis
Windows XP, Office 2003


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> >
>> >
>
>

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

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.

--
Regards

Roger Govier


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>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)
>
> "Roger Govier" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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" <[email protected]> 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" <[email protected]> wrote in
>> >> message
>> >> news:[email protected]:
>> >>
>> >> > 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" <[email protected]> wrote in message
>> >> > news:%[email protected]...
>> >> > > 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" <[email protected]>
>> >> > > wrote in
>> >> > message
>> >> > > news:[email protected]...
>> >> > > |
>> >> > > | 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
>> >> > > |
>> >> > >
>> >> > >
>> >>
>> >
>> >
>>
>>
>
>

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>