Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with *Friday the 13th*. Something like this:

[Related: Finding next Friday the 13th using formulas]

### Finding all Friday the 13ths in a year

Here is a formula to find the first Friday the 13th in a year.

{=IFERROR(SMALL(IF(WEEKDAY(DATE($C$3,ROW($A$1:$A$12),13))=6,ROW($A$1:$A$12)),1),””)}

Let’s understand how it works, going from inside out:

**DATE($C$3,ROW($A$1:$A$12),13) portion: **This generates an array of 12 dates, one for each 13th of the month in the year C3.

**WEEKDAY(DATE(…))=6 portion: **This checks how many of those dates are Fridays. Returns an array of TRUE / FALSE values.

**IF(WEEKDAY(DATE(…))=6,ROW($A$1:$A$12)) portion: **This returns an array of month numbers where we have Friday the 13th and FALSE values.

**SMALL(IF(…),1) portion: **This will give us the first month (ie 1st smallest value) with Friday the 13th in the year C3.

**IFERROR() **to suppress any errors.

* To show all the Friday the 13ths in a year,* simply replace

**1**with an array of numbers (up to 3 should do).

### Download Friday the 13th finder workbook

**Click here to download Friday the 13th finder workbook**. Enter a year in cell C3 and see all the months with Friday the 13th instantly.

### A challenge for you…

If you are still reading, I have a challenge for you. Can you write a formula to **find the next year with three Friday the 13ths**? Assume the year is in C3. Post your formulas / VBA in the comment section.

*hint: in the downloadable workbook, you can find an answer for this.*

So that’s all for now. Enjoy your Friday the 13th.

## 13 Responses to “How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]”

in C3=2016

in C4=3

in C5=1 (the first next year with three Friday the 13ths)

=SMALL(IF(MMULT(--(MOD(DATE(C3+ROW(1:1000),COLUMN(A:L),13),7)=6),ROW(1:12)^0)=C4,C3+ROW(1:1000)),C5)

formula check in the next 1000 years

This will generate a table of counts of Friday the 13th's by year. If I didn't screw it up the next year with three is 2026.

I created a simple parameter table with a start date and end date that I wanted to evaluate. That calculates the number of days and generates a list of those days. Then filter and group. The generation of the list in power query (i.e. without populating a date table in excel) is pretty cool, otherwise this isn't really doing anything than creating a big date and filtering/counting.

let

Source = List.Dates(StartDateAsDate, Days2, #duration(1,0,0,0)),

ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),

AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),

AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),

FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),

Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})

in

Friday13thsByYear

With the parameters replaced by values should you want to play along at home. This runs for 20 years starting on 1/1/2016.

let

Source = List.Dates(#date(2016,1,1), 7300, #duration(1,0,0,0)),

ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),

AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),

AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),

FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),

Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})

in

Friday13thsByYear

=MATCH(3,MMULT(N(WEEKDAY(DATE(C3+ROW(1:100)-1,COLUMN(A:L),13))=6),1^ROW(1:12)),)+C3-1

It should be pointed out that Alex's solution, unlike some others, has the additional advantage of being non-array. My solution was nearly identical but with -- and SIGN instead of N and 1^.

=C3-1+MATCH(3,MMULT(--(WEEKDAY(DATE(C3-1+ROW(1:25),COLUMN(A:L),13))=6),SIGN(ROW(1:12))),0)

Sub Friday13()

Dim StartDate As Date

Dim EndDate As Date

Dim x As Long

Dim r As Long

Range("C7:C12").ClearContents

StartDate = CDate("01/01/" & Range("C3"))

EndDate = CDate("31/12/" & Range("C3"))

r = 7

For x = StartDate To EndDate

If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then

Cells(r, 3) = Month(x)

r = r + 1

End If

Next

End Sub

Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3

Sub ThreeFriday13()

Dim StartDate As Date

Dim EndDate As Date

Dim x As Long

Dim WhatYear As Integer

Dim Counter As Integer

Range("E7").ClearContents

StartDate = CDate("01/01/" & Range("C3") + 1)

EndDate = CDate("31/12/" & Range("C3") + 100)

Counter = 0

For x = StartDate To EndDate

If WhatYear Year(x) Then

WhatYear = Year(x)

'Different year so reset counter

Counter = 0

End If

If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then

Counter = Counter + 1

If Counter = 3 Then

WhatYear = Year(x)

Exit For

End If

End If

Next

Range("E7") = WhatYear

End Sub

*RE-POST as not equal did not show earliuer

Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3

Sub ThreeFriday13()

Dim StartDate As Date

Dim EndDate As Date

Dim x As Long

Dim WhatYear As Integer

Dim Counter As Integer

Range("E7").ClearContents

StartDate = CDate("01/01/" & Range("C3") + 1)

EndDate = CDate("31/12/" & Range("C3") + 100)

Counter = 0

For x = StartDate To EndDate

If WhatYear NE Year(x) Then

WhatYear = Year(x)

'Different year so reset counter

Counter = 0

End If

If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then

Counter = Counter + 1

If Counter = 3 Then

WhatYear = Year(x)

Exit For

End If

End If

Next

Range("E7") = WhatYear

End Sub

I've a doubt with using array formula here.

In sample workbook, I tried to replicate the formula again.

=IFERROR(SMALL(IF(WEEKDAY(DATE($C$3,ROW($A$1:$A$12),13))=6,ROW($A$1:$A$12)),$B7),"")

For this I selected C7 to C12, and typed the same formula and pressed ctrl+alt+Enter. But in all cells it is taking $B7 (and not $B7, $B8, $B9.... etc)

and since it is array formula I can't edit individual cell.

Please guide.

Thanks

Hi Chandoo,

Cool stuff. You need to clarify that the answer of 5 represents the 1st month in the year that has a Friday the 13th, and not the number of Fridays the 13th in the year. Subtle, but important difference.

Thanks,

Pablo

I like the MMULT() function far more, but here's how I would have tackled it. It uses an EDATE() base and MODE() over 100 years. I'm assuming that 100 years is enough time to catch the next year with 3 friday 13th's. Array entered, of course.

{=MODE(IFERROR(YEAR(IF((WEEKDAY(EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))))=6), EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))), "")), ""))}

Finding all the Friday the 13ths in a Year:

=SUMPRODUCT((DAY(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))))=13)*(TEXT(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))),"ddd")="Fri"))

{=sum(if(day.of.week(DATe($YEAR;{1;2;3;4;5;6;7;8;9;10;11;12};13);1)=6;1;0))}

just list the years