Friday 13
- Dec. 26, 2020
 | Example files with this article: |
Friday the 13th
Friday the 13th is considered an unlucky day in Western superstition. "It occurs when the 13th day of the month in the Gregorian calendar falls on a Friday, which happens at least once every year but can occur up to three times in the same year." Can we check that Wikipedia is right about having 1 to 3 such Fridays in any given year ? Interestingly, certain societies have the 17th as an unlucky day and even other societies have Tuesday as an unlucky day. I can relate to the latter: weekend is long away while for a Friday it's close !
Yes we can do that, cetainly with the new dynamic array functions:
Formulas were entered in cells C2, D2, E2, F2. That's all it takes. The first 3 formulas spill down to below, rows 3 all the way to 13 (coincidentally).
Formulas that spill are indicated with a blue border. Also, only the starting cell contains a formula while the other cells in the spill range do not contain a formula. The formula in cell C2 equals: =SEQUENCE(12) and is editable, the cells below show the same formula but are non-editable.
Near the bottom of the first picture, you can have a look at the formulas I used. The # character deserves some explanation as it is fairly new in Excel. It tells the calculation engine to take the spilled range, rather than only the single cell. Thus, it will allows us to make very dynamic calculations, generating results over several cells.
=WEEKDAY( D2#, 2 ) gives us 1 for Monday, 2 for Tuesday, ..., 7 for Sunday. Hence, also 5 for Friday. That's what we are interested in.
In the decade we are living in, which years contain 1 / 2 / 3 such Fridays ?
The formula is shown over to the right. The double unary operator -- turns True/False into 1/0, such that an easy sum is possible.
Lastly, what are those months, in a certain year, that have a Friday the 13th ?
I used the LET() function to simplify the formula: =LET( d, DATE( A1, SEQUENCE( 12 ), 13 ), FILTER( d, WEEKDAY( d, 2 ) = 5 ))
I use the variable d for the 12 dates in the chosen year (cell A1). So Jan. 13, Feb. 13, Mar. 13, ... Then we have the actual formula as the last argument of the LET function, which says: filter out those 12 dates where the weekday number (Monday through Sunday) gives a 5 for Friday. Bingo, you have the 1 / 2 / 3 dates. Format the results with numberformatting as: mmmm: dd/mm/yyyy and you are done. FILTER is another rather new formula in Excel.
Happy holidays !
