Someone claimed recently that most 13th of the months fell on Fridays in recent years. This claim came upon the heels of my blog on how to find Friday-13ths for any year in Excel. So, obviously I had to check it out for myself if the claim is true. And I want to check it systematically, automatically, without having to count or even search for data on the internet. Here I share the process of doing that so that it can span any number of years without changing the underlying method or formulas at all.
To start, let’s scope the “recent years” to 10 years as agreed with the claimant. So, I’ll cover 2010 through 2019. However, my Excel sheet can cover any span that your system can support.
Proof & Method
This is how I will approach this: Find all days in each month of each year spanning 10 years through 2019. Then count each day that fell on 13th. Then find the frequency of which weekday 13th fell on for the whole year…do it for 10 years. Then find the frequency of all 10 years and find the most frequent day of the week on which 13th occurred.
In Excel, my layout is as below (it can also be transposed if needed). 
The cells within this matrix will be calculated for just 1 cell! Then filled in for the entire range for the year. Then the formula will be copied over to the next year’s column by simply changing one parameter (the year of course), and dragged over for as many columns (i.e. years) as needed.
Under each year, I have 2 columns. One for showing which weekday (in numerical form, as it’s the quickest for Excel) 13th falls on, and to its left another column which will show me the month (in a numerical representation) in which Friday 13th occurred (this is optional since we don’t need to count Friday 13th or its months for this proof, but I did it to double-verify the results).
Using nested IF(), WEEKDAY(), DATE() and ROW() functions as explained in my previous blog, I’m able to fill in all the required information for my first step to automatically generate the data.
Now, the populated table looks like this:
The weekday column shows the number of the weekday where 1=Sunday, 2=Monday and so on. The Month column shows either FALSE or the number of the month, where 1=January, 12=December and so on.
The next step is to find which weekdays occurred the most for each month. This can be quickly done by using the statistical multi mode function (although for a range of 7 days, we could get away with singular mode function) such as MODE.MULT() for each month’s range.
Once I have the most frequent weekdays for 13th for all the months, I do another MODE.MULT() on the total to get the overall most frequent weekday for the entire span of 10 years.
To make it even more readable, I convert the weekday numbers to names using VLOOKUP or IFS.
The final table now looks like this:
As we can see here, Wednesday was the most frequent weekday in which 13th occurred.
The Verdict
The claim is false. Most 13th day of the months for 10 years DID NOT fall on Fridays, it fell on Wednesdays.
Wanna cover about 50 years? 100 years span? No problem. The same method and formulas will work, just drag the formulas out. You can also check out the previous blog here.
Challenge
On which days did 1st of the month fall in the past 10 years?
On which dates did Thanksgiving fall in the past 5 years?
On which days did Christmas fall in the past 20 years?
