|
Date Calculation
How do I calculate how many times a given date 11/13 occured on a Friday
since 1953 to present? |
Date Calculation
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? |
Date Calculation
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 *** |
Date Calculation
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. |
Date Calculation
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 |
Date Calculation
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 |
Date Calculation
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 |
Date Calculation
On Sun, 4 Jan 2009 17:03:20 -0000, "Bob Phillips"
wrote: It's 2009 now :-) Yes, but not yet November 13... ;-) |
Date Calculation
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? |
Date Calculation
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")) |
Date Calculation
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 |
Date Calculation
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 |
Date Calculation
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... ;-) |
Date Calculation
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)) |
Date Calculation
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 |
| All times are GMT +1. The time now is 11:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com