18.2 Tips on Rounding numbers using Excel Formulas

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

1. roberto mensa says:

   May 13, 2016 at 10:11 am

   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

   Reply
2. Brian says:

   May 13, 2016 at 1:14 pm

   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

   Reply
   • Brian says:

     May 13, 2016 at 1:19 pm

     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

     Reply
3. Alex Groberman says:

   May 13, 2016 at 3:25 pm

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

   Reply
   • David N says:

     May 18, 2016 at 4:23 pm

     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)

     Reply
4. SunnyKow says:

   May 13, 2016 at 4:52 pm

   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

   Reply
   • SunnyKow says:

     May 13, 2016 at 5:25 pm

     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

     Reply
     • SunnyKow says:

       May 13, 2016 at 5:27 pm

       *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

       Reply
       • goby says:

         September 21, 2020 at 9:49 pm

         earlier*

         Reply
5. Devesh says:

   May 14, 2016 at 4:14 am

   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

   Reply
6. Pablo says:

   May 16, 2016 at 3:30 pm

   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

   Reply
7. Micah Dail says:

   May 17, 2016 at 2:12 pm

   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"))), "")), ""))}

   Reply
8. Jason Morin says:

   May 20, 2016 at 1:42 am

   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"))

   Reply
9. jmdias says:

   November 14, 2016 at 8:44 pm

   {=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

   Reply

Leave a Reply