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

Finding Friday the 13th using Excel (and learning cool formulas along way):
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.
We 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)