B
Bill
How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present?
since 1953 to present?
G
Gary''s Student
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
=DATE(1952+ROW(),11,13)
In B1 enter:
=TEXT(A1,"DDD") and copy down thru B56
Finally =COUNTIF(B:B,"Fri") will display 8
G
Glenn
Bill said:
=SUM(IF(WEEKDAY(DATE(ROW(1953:2008),11,13))=6,1,0))
*** Array Formula - Commit with CTRL+SHIFT+ENTER ***
G
Glenn
Glenn said:
Shorter:
=SUM(--(WEEKDAY(DATE(ROW(1953:2008),11,13))=6))
Also an Array Formula.
L
Lars-Åke Aspelin
Try this formula:
=SUMPRODUCT(--(WEEKDAY(DATE(ROW(1953:2008),11,13))=6))
Hope this helps / Lars-Åke
B
Bob Phillips
It's 2009 now 
D
David Biddulph
And is it already past 13th November 2009 in your timezone, Bob?
L
Lars-Åke Aspelin
It's 2009 now
Yes, but not yet November 13... ;-)
S
Shane Devenshire
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 future:
=SUMPRODUCT(--(MOD(DATE(ROW(53:199),11,13),7)=6),--(DATE(ROW(53:199),11,13)<=TODAY()))
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 future:
=SUMPRODUCT(--(MOD(DATE(ROW(53:199),11,13),7)=6),--(DATE(ROW(53:199),11,13)<=TODAY()))
B
barry houdini
....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())),"ddd-dd-
mmm")="Fri-13-Nov"))
L
Lars-Åke Aspelin
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
R
Ron Rosenfeld
=SUMPRODUCT(--(WEEKDAY(DATE(ROW(INDIRECT(
1953 &":"&YEAR(TODAY())-(TODAY()<DATE(YEAR(
TODAY()),11,13)))),11,13))=6))
Format as General or as Number
--ron
B
Bob Phillips
But why would you restrict it, especially to 2008. Why not be dynamic?
B
barry houdini
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))
L
Lars-Åke Aspelin
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