If you’re anything like me, you’ve at least once wondered: How can I get Excel to show me all the Friday-13ths in any given year?
Well, I have (don’t ask me why)…and here’s the solution. I found a couple of ideas on how to do this, but my method I believe, is the most straight-forward and I’ve simplified it while keeping it 100% accurate. And it requires no VBA coding.
I want to just enter any year and I want to see ALL the months that will have (or not have) a Friday 13th occur in that year.
The Method
First, find each 13th day of each month of the user-entered year…for all 12 months. Reference array of 12 is given to ROW function as ROW($A$1:$A$12) but I can simplify it to ROW(1:12) since there’s no real reason for specific cell references, just enough space in array for 12 months. This will return a serial number for the date that Excel uses. For example, 1/13/2019 is 43478.
Next, using that serial number, we need to find out what day of the week each of the 13th day falls in. Sunday through Saturday. It’ll return a single digit that refers to the name. By default, Sun=1, Mon=2 and so son.
Finally, we need to check which of these weekdays returned belongs to friday (6 would be Friday), and return a number that corresponds to the month. 1 would be Jan, 12 would be December and so on.
The result will be displayed in a range of cells as it has to return an array since we have to cover all 12 months. For every month, the 13th is a friday, the number of the month will appear, otherwise, it’ll show as FALSE. Because we use IF() to test which returns true or false…but we tweak it so say, if true, then show the month number, otherwise just return the usual FALSE. (This can be further modified into mapping the number of the month to the name of the month, and instead of showing FALSE, show “nothing” or something else…those require additional nesting of functions which I deliberately left out of this for bravity).
The Formula
Assuming the input cell where the year is entered is C3, and you have at 12 blank rows to display the results (otherwise, you’ll get #SPILL error); then pick a starting row and put in the following formula:
=IF(WEEKDAY(DATE($C$3,ROW($A$1:$A$12),13))=6,ROW($A$1:$A$12))
or
=IF(WEEKDAY(DATE($C$3,ROW(1:12),13))=6,ROW(1:12))
There you have it! For 2019, we see results are months: 9, 12. Meaning we have two friday-13ths. One in September, another in December.
For 2020, there wil be one in March, and another in November. Now you can plan your mischiefs for the future years ahead of time 😉
Try it!
Try it online here. Enter a year of your choice in the green cell.
You may also enjoy this related blog.
