Finding Friday the 13th using Excel (and learning cool formulas along way)

Posted on January 13th, 2012 in Formula Forensics , Learn Excel - 17 comments

Not that I have friggatriskaidekaphobia or anything. But since today is Friday & 13th, lets put our Excel skills to test and find out when the next Friday the 13th is going to be.

(trivia: Check this for some interesting facts about Friday the 13th)

Finding Next Friday the 13th using Excel Formulas – Approach 1

Lets say, you have a date in cell C3, and you want to find out when the next Friday, the 13th is going to be starting the date in cell C3.

The first approach I can think of is pretty straight forward.

Finding Friday the 13th using ExcelWe list all the 13ths in a column and find the next 13th which is also a Friday. For this,

  1. In cell E3, we write =MONTH(C3)
  2. In cell F3, we write =YEAR(C3)
  3. We use these 2 cells to refer to the month and year of the starting date.
  4. Then, we write in an empty cell =DATE($F$3,$E$3+ROWS($A$1:A1),13) – lets say this cell is E5
  5. This gives us the 13th date of next month, from the start date in cell C3.
  6. Now, lets drag this formula and fill it down, for say, next 100 cells to get next 100 13ths.
  7. The ROWS($A$1:A1) portion generates continuous numbers from 1 thru 100 and thus we get next 100 13ths.
    For more on this technique, read – Using ROWS() to generate a series of numbers

Once all the 13ths are listed, in an adjacent column, we can use WEEKDAY() formula to see if the 13th is a Friday – WEEKDAY(E5)=6

This column will have a bunch of TRUE & FALSE values.

Now to find the next Friday the 13th, we just look for TRUE value in this column (say F5:F104) use it to derive the date.

So this formula =DATE(F3,E3+MATCH(TRUE,$F$5:$F$104,0),13) should give us the next Friday, the 13th.

Break up of above formula:

  1. MATCH(TRUE,$F$5:$F$104,0) tells us the position of first TRUE value (ie first Friday, the 13th in our list)
  2. DATE(F3,E3+Match value, 13) gives the date of next Friday, the 13th
  3. Remember, F3 contains the year and E3 the month of starting date you entered in C3.

Finding Friday the 13th, 2nd Approach

While above approach works fine, it requires a few helper columns. So I got thinking, how can we write a one shot formula that gives us next Friday, the 13th date?

First the formula:

This is an array formula.

{ =DATE(YEAR($C$3), MATCH(TRUE, WEEKDAY(DATE(YEAR($C$3),MONTH($C$3)+ROW($A$1:$A$100), 13))=6,0) + MONTH($C$3),13) }

Scary formula indeed. We may have to coin a word for fear of long excel formulas – doubleXLformulaphophia.

How does this formula work?

Before understanding the portions of this formula, we need to understand the approach.

This formula uses similar thinking as of earlier formula. Just that it shrinks all those helper columns to an array and works the magic.

To find next Friday, the 13th, we need to list down next few 13ths and check which one is a Friday. Since Excel lookup formulas always return the first match, we find the first such Friday.

Parts of the formula:

  • To get the next 100 13ths, we use, DATE(YEAR($C$3),MONTH($C$3)+ROW($A$1:$A$100), 13)

When used in an array formula, this gives us the 13th days of next 100 months.

(aside: technically, we do not need next 100 months. As per Wikipedia, the maximum gap between successive Friday, the 13ths is 14 months. more)

Also, note that we are using ROW() formula, not ROWS(), as we want all the row numbers for first 100 rows as an array.

  • Once we have these 100 dates, we just check for their Fridayness with, WEEKDAY(100 dates))=6

This formula returns a 100 TRUE & FALSE values. TRUE, whenever the date is a Friday, FALSE, when it is not.

  • Then, we find the first TRUE value (ie first occurrence of Friday, the 13th in next 100 months) with, MATCH(TRUE, next 100 dates’ Fridayness, 0)

This gives us the position of next TRUE value.

  • Finally, we use that to construct the date of next Friday, the 13th – DATE(YEAR($C$3), MONTH($C$3) + first TRUE value, 13)

And that is how we find the next Friday the 13th based on the start date in cell C3.

Important Note:

Both approaches only search for Friday, the 13th starting next month of the date in C3. If C3 has a date prior to 13th and the 13th of that month is a Friday, the 13th, it would not be considered. For example, if you enter 10-JAN-2012 in C3, both formulas would find next Friday the 13th as April 13, 2012 not Jan 13, 2012.

Download Friday, the 13th Example Workbook

I have made a colorful (and almost gory) download workbook. Even if you do not want to learn this, I suggest downloading the file, for fun!

Click here to download the Friday, 13th calculations workbook & play with it.

Bonus: It has homework too!

Your Homework

Finally some homework to wrap up this week.

Write a formula to find the maximum gap between consecutive Friday, the 13ths in next 5 years, from a starting date in cell C3.

Please post your answers in comments so that we all can learn.

Checkout more Formula Forensics

Once in a while, we take a complex real world (or as in this case, gory world) problem and write an equally scary formula. Then, we go great lengths breaking it down and explaining it. We call this as Formula Forensics. Much like forensics in CSI, without ultra zoom & hot chicks. You can check out some of our recent adventures here:

  1. Using an array formula to count maximum occurrences of a text
  2. Counting specific words in a multi-cell range
  3. Extracting a list of items from a larger list by criteria
  4. More formula forensics

PS: It is also Hui’s birthday today. Lets wish him many more years of fun, happiness & Excel craze.

PPS: Finding his next birthday is going to be simple, we just write =DATE(2013,1,13) :P

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

17 Responses to “Finding Friday the 13th using Excel (and learning cool formulas along way)”

  1. Juan Carlos Etayo says:

    excellent..!!!

  2. Jason says:

    I think this will work. Must be set as an arrary formula =MAX(DATEDIF(A1:A12,A2:A13,”m”))

    This method calculated the max difference to be 14 months. (July 2012 to September 2013)

  3. Luke M says:

    Well, this exceeds the 7 function nest limit in pre-2007 workbooks, but this the array formula I’ve got so far:
    =MAX(LARGE(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13)),ROW(INDIRECT(“A1:A”&COUNT(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13)))-1)))-LARGE(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13)),ROW(INDIRECT(“A2:A”&COUNT(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13)))))))

    Idea is to use 2 sets of Chandoo’s original formula to create arrays, then use the LARGE function to compare them by taking the differences. (note that it compares the 1:(n-1) largest with the 2:n largest. Then use a MAX to get the largest difference. With a date of Jan 13th, 2012 in C3, I get a result of 427, the gap between 9/13/2013 and 7/13/2012. I’m going to work now on reducing the nesting level.

  4. Luke M says:

    Here’s my alternate way that I don’t believe exceeds the 7 nest limit:
    =MAX(IF(LARGE(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13),0),ROW(A1:A59))-LARGE(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13),1),ROW(A2:A60))<1000,LARGE(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13),0),ROW(A1:A59))-LARGE(IF(WEEKDAY(DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13))=6,DATE(YEAR(C3),MONTH(C3)+ROW(A1:A60),13),1),ROW(A2:A60))))

    Plugs in a 1 instead of FALSE in Chandoo's portion of the formula, and then uses an overall IF check to sort out any differences that are outside the needed range.

  5. Anar says:

    For the first approach I have the following alternative:
    – I would write first date (1/13/2012) in A1
    – Then would drag it down by holding mouse’s right button and fill months. This will give us 13th of each month
    – Then in B1 would use simple WEEKDAY formula (=WEEKDAY(A1,2))
    “5” will represent Friday.
    – Then would count number of “5”s, using Countif function for example.

  6. Squiggler says:

    Here’s a little formula to give you all the friday 13th’s in any year!

    =”Friday 13’s for the year “&M19&” are in months “&SUBSTITUTE(TEXT(SUMPRODUCT({10000,100,1}*(0&MID(“05 0110 0407 0912 06 02031105 10 0104070912 06 0311 0208 0110 “,{1,3,5}+(((MONTH(DATE(M19,2,29))=2)*7+MOD(DATE(M19,1,1),7))*6),2))),”00\,00\,00″),”,00″,””)

  7. Squiggler says:

    Or simpler
    =”Friday 13’s for the year “&L23&” are in months “&SUBSTITUTE(TEXT(SUMPRODUCT(LARGE(–(MOD(DATE(L23,{1,2,3,4,5,6,7,8,9,10,11,12},13),7)=6)*{1,2,3,4,5,6,7,8,9,10,11,12},{3,2,1})*{10000,100,1}),”00\,00\,00″),”00,”,””)

    • Squiggler says:

      And to find the next friday the 13th, try this little array formula :-

      =MATCH(“13.Fri”,TEXT(ROW(INDIRECT(“1:366″))+C2,”dd.ddd”),0)+C2

    • Squiggler says:

      And to find the next friday the 13th, try this little array formula :-

      =MATCH(“13.Fri”,TEXT(ROW(INDIRECT(“1:366″))+C2,”dd.ddd”),0)+C2

      Enter with CTRL+SHIFT+ENTER

  8. Squiggler says:

    1:366 should be 1:430 since the maximum period between Friday the 13th is 427 days!

  9. sam says:

    Assuming A1 to have a friday the 13th
    =EDATE(A1,MATCH(6,WEEKDAY(EDATE(A1,ROW(A1:A430))),0))

  10. Aaron says:

    Here is an update to your array formula that will allow you to find the next friday the 13th even if you are in the same month that it occurs. The original formula only searched dates in months after the current month.

    DATE(YEAR($C$3),MATCH(TRUE, WEEKDAY(DATE(YEAR($C$3),IF(DAY($C$3)<=13,0,MONTH($C$3))+ROW($A$1:$A$100),13))=6,0)+IF(DAY($C$3)<=13,0,MONTH($C$3)),13)

  11. Dnice Longshaw says:

    All I can say is “WOW”

    You guys are amazing.

  12. JohnC_UK says:

    @Sqiggler

    I love your lateral thinking!

  13. Narinder Kumar says:

    amazing amazing ama . . . . ..  . .. . .  . . . .. .

  14. aaqib says:

    just write a start date 13-jan-2012 apply the formula =edate(13-jan-2012,1) and in the b column apply formula =if(text(a1,”ddd”)=”fri”,a1,””) its sorts all the fri13th after this use the filter and visible sales and paste……..

  15. Krishna Teja says:

    427
    {=MAX(‘Approach 2’!C7:C18-‘Approach 2’!C6:C17)}

Leave a Reply