How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?

In A1 thru A56 enter:

=DATE(1952+ROW(),11,13)

In B1 enter:

=TEXT(A1,"DDD") and copy down thru B56

Finally =COUNTIF(B:B,"Fri") will display 8
--
Gary''s Student - gsnu200823


"Bill" wrote:

How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?

Bill wrote:
How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?


=SUM(IF(WEEKDAY(DATE(ROW(1953:2008),11,13))=6,1,0) )

*** Array Formula - Commit with CTRL+SHIFT+ENTER ***

Glenn wrote:
Bill wrote:
How do I calculate how many times a given date 11/13 occured on a
Friday since 1953 to present?


=SUM(IF(WEEKDAY(DATE(ROW(1953:2008),11,13))=6,1,0) )

*** Array Formula - Commit with CTRL+SHIFT+ENTER ***


Shorter:

=SUM(--(WEEKDAY(DATE(ROW(1953:2008),11,13))=6))

Also an Array Formula.

On Sun, 4 Jan 2009 08:01:01 -0800, Bill
wrote:

How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?


Try this formula:

=SUMPRODUCT(--(WEEKDAY(DATE(ROW(1953:2008),11,13))=6))

Hope this helps / Lars-Åke

It's 2009 now :-)

--
__________________________________
HTH

Bob

"Lars-Åke Aspelin" wrote in message
...
On Sun, 4 Jan 2009 08:01:01 -0800, Bill
wrote:

How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?


Try this formula:

=SUMPRODUCT(--(WEEKDAY(DATE(ROW(1953:2008),11,13))=6))

Hope this helps / Lars-Åke

And is it already past 13th November 2009 in your timezone, Bob? :-)
--
David Biddulph

Bob Phillips wrote:
It's 2009 now :-)

"Lars-Åke Aspelin" wrote in message
...
On Sun, 4 Jan 2009 08:01:01 -0800, Bill
wrote:

How do I calculate how many times a given date 11/13 occured on a
Friday since 1953 to present?


Try this formula:

=SUMPRODUCT(--(WEEKDAY(DATE(ROW(1953:2008),11,13))=6))

Hope this helps / Lars-Åke

On Sun, 4 Jan 2009 17:03:20 -0000, "Bob Phillips"
wrote:

It's 2009 now :-)

Yes, but not yet November 13... ;-)

Hi,

You can use the following array formula: (press Shift+Ctrl+Enter)

=SUM(--(MOD(DATE(ROW(53:108),11,13),7)=6))
or its not array equivalent
=SUMPRODUCT(--(MOD(DATE(ROW(53:108),11,13),7)=6))

However, these solution will need to be adjusted on 11/13 or each year.
Here is a formula that eliminates that need and works far into the futu

=SUMPRODUCT(--(MOD(DATE(ROW(53:199),11,13),7)=6),--(DATE(ROW(53:199),11,13)<=TODAY()))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Bill" wrote:

How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?

On Jan 4, 5:40*pm, Shane Devenshire
wrote:
Hi,

You can use the following array formula: (press Shift+Ctrl+Enter)

=SUM(--(MOD(DATE(ROW(53:108),11,13),7)=6))
or its not array equivalent
=SUMPRODUCT(--(MOD(DATE(ROW(53:108),11,13),7)=6))

However, these solution will need to be adjusted on 11/13 or each year. *
Here is a formula that eliminates that need and works far into the futu

=SUMPRODUCT(--(MOD(DATE(ROW(53:199),11,13),7)=6),--(DATE(ROW(53:199),11,13)*<=TODAY()))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire



"Bill" wrote:
How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?- Hide quoted text -

- Show quoted text -

....although using MOD instead of WEEKDAY means you could get the wrong
result if you are using 1904 date system............

This formula is less efficient because it examines every day but it'll
give the correct number from 1st Jan 1953 to the current date

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(DATE(1953,1,1)&":"&TODAY())),"d dd-dd-
mmm")="Fri-13-Nov"))

On Sun, 4 Jan 2009 13:10:52 -0800 (PST), barry houdini
wrote:

On Jan 4, 5:40*pm, Shane Devenshire
wrote:
Hi,

You can use the following array formula: (press Shift+Ctrl+Enter)

=SUM(--(MOD(DATE(ROW(53:108),11,13),7)=6))
or its not array equivalent
=SUMPRODUCT(--(MOD(DATE(ROW(53:108),11,13),7)=6))

However, these solution will need to be adjusted on 11/13 or each year. *
Here is a formula that eliminates that need and works far into the futu

=SUMPRODUCT(--(MOD(DATE(ROW(53:199),11,13),7)=6),--(DATE(ROW(53:199),11,13)*<=TODAY()))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire



"Bill" wrote:
How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?- Hide quoted text -

- Show quoted text -

...although using MOD instead of WEEKDAY means you could get the wrong
result if you are using 1904 date system............

This formula is less efficient because it examines every day but it'll
give the correct number from 1st Jan 1953 to the current date

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(DATE(1953,1,1)&":"&TODAY())),"d dd-dd-
mmm")="Fri-13-Nov"))


That formula assumes that the "ddd-abbrevation" for Friday is "Fri"
and the "mmm-abbreviation" for November is "Nov" which is dependant on
language settings of the individual computer so I would not recommend
that one.

To be on the safe side you could replace "Fri-13-Nov" with
TEXT(DATE(1998,11,13),"ddd-dd-mmm")

But in my Excel you have to write "MMM" instead of "mmm" so maybe it
is not 100% safe anyway if the formatting codes are also language
dependant.

Hope this helps / Lars-Åke

On Sun, 4 Jan 2009 08:01:01 -0800, Bill wrote:

How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?

=SUMPRODUCT(--(WEEKDAY(DATE(ROW(INDIRECT(
1953 &":"&YEAR(TODAY())-(TODAY()<DATE(YEAR(
TODAY()),11,13)))),11,13))=6))

Format as General or as Number

--ron

But why would you restrict it, especially to 2008. Why not be dynamic?

--
__________________________________
HTH

Bob

"Lars-Åke Aspelin" wrote in message
...
On Sun, 4 Jan 2009 17:03:20 -0000, "Bob Phillips"
wrote:

It's 2009 now :-)

Yes, but not yet November 13... ;-)

On Jan 4, 9:53*pm, Lars-Åke Aspelin wrote:
On Sun, 4 Jan 2009 13:10:52 -0800 (PST), barry houdini





wrote:
On Jan 4, 5:40*pm, Shane Devenshire
wrote:
Hi,

You can use the following array formula: (press Shift+Ctrl+Enter)

=SUM(--(MOD(DATE(ROW(53:108),11,13),7)=6))
or its not array equivalent
=SUMPRODUCT(--(MOD(DATE(ROW(53:108),11,13),7)=6))

However, these solution will need to be adjusted on 11/13 or each year.. *
Here is a formula that eliminates that need and works far into the futu

=SUMPRODUCT(--(MOD(DATE(ROW(53:199),11,13),7)=6),--(DATE(ROW(53:199),11,13)**<=TODAY()))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Bill" wrote:
How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?- Hide quoted text -

- Show quoted text -

...although using MOD instead of WEEKDAY means you could get the wrong
result if you are using 1904 date system............

This formula is less efficient because it examines every day but it'll
give the correct number from 1st Jan 1953 to the current date

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(DATE(1953,1,1)&":"&TODAY())),"d dd-dd-
mmm")="Fri-13-Nov"))

That formula assumes that the "ddd-abbrevation" for Friday is "Fri"
and the "mmm-abbreviation" for November is "Nov" which is dependant on
language settings of the individual computer so I would not recommend
that one.

To be on the safe side you could replace "Fri-13-Nov" with
TEXT(DATE(1998,11,13),"ddd-dd-mmm")

But in my Excel you have to write "MMM" instead of "mmm" so maybe it
is not 100% safe anyway if the formatting codes are also language
dependant.

Hope this helps / *Lars-Åke- Hide quoted text -

- Show quoted text -

I take your point, Lars

perhaps a modification to Ron's suggestion using DATEDIF, i.e.

=SUMPRODUCT(--(WEEKDAY(DATE(ROW(INDIRECT("1:"&DATEDIF(DATE
(1953,11,13),TODAY(),"Y")+1))-1+1953,11,13))=6))

On Sun, 4 Jan 2009 22:24:12 -0000, "Bob Phillips"
wrote:

But why would you restrict it, especially to 2008. Why not be dynamic?


I agree with you that it should not in general be restricted.

My interpretation of the word "present" in the OP was however that
this was a "one time shot" just to find the answer to the question
today/now, rather than something to be included in a spreadsheet to be
use "forever".
One problem with the human languge, especially when it's not your
mother tounge, is that it is very often not 100% clear what we try to
express.

Anyway, I think the OP has now got a number of proposals to choose
among.

Lars-Åke