@ > Home > Contents > Arriving Friday 13 ... Excel 97+

Task (with 4 solutions):

Show all consecutive days 13 which are Fridays.

Solution 1 - A (only)  testing array formula:

A1: 01-12-2080 (example of interval's end)
A2: 6 (Friday; Saturday is beginning at 0 again)
A3: 13 (the day looked for)

C1: 01-01-2007 (begin of interval)
C2: {=(C1<$A$1)*(C1>=$C$1)*MIN(-SUBSTITUTE("-"&(C1+ROW($1:$609))*(MOD(C1
+ROW($1:$609),7)=$A$2)*(DAY(C1+ROW($1:$609))=$A$3),"-0",-99999))}

C2 is an array formula, to be entered without {} by pressing Ctrl-Shift-Enter.

For the example, you need to fill down the formula to C130.

This solution needs rather heavy computing power (609*129 formulas) since the length of loop of 609 is fix. A VBA function would help only a little because the expected average exit would be at 305.

If C1 already is a valid date, the faster term ROW(28:609) instead of ROW(1:609) is sufficient because a successor comes after a minimum of 4 weeks.

Solution 2 - A calculating formula. It is restricted to the use of the days 1-28: (optimized 2007/04/26)

Consecutive months with the same weekdays can also be calculated, rather than loop-wise be tested:

Decoding the string of the following formula by =CODE()-96, it results in the following month with identical weekdays, for normal years using the first 12 and for leap years the last 12 characters: 

C3: =DATE(YEAR(C2),CODE(MID("jckgmnuqlptrdhkgmnrvluqo",MONTH(C2)+MOD(SUMPRODUCT(
--(MOD(YEAR(C2)+(OR(MONTH(C2)={7,8,10,11,12})),{400,100,4})=0)),2)*12,1))-96,DAY(C2))

The days 29-31 need an additional treatment and will not work with it.

Note: The Excel limit of 8 nested functions is reached. Therefore a logical split (by Insert Name Define) is recommended. 

MOD(SUMPRODUCT(--(MOD(YEAR(C2),{400,100,4})=0)),2)

calculates whether the date C2 contains a leap year. *12 positions to the right character in the string.

(OR(MONTH(C2)={7,8,10,11,12})) shifts the leap year of the date. It means: These 5 months in the leap-year-preceding year have a successor after the leap of the following year.

The formula in C3 can be filled down; it can be extended with  (C1<$A$1)*(C1>=$C$1), see solution1. C2 is further needed, because it processes the two parameters A2 and A3. In other words: C2 defines the weekday and date-day for C3ff.  - or: C3 does not care about A1,A2,A3,C1 itself.

The speed increase against solution 1 is incredible!

Solution 3 - A calculating VBA function for all days

The very fast function (see here in German, but of course working in every native Excel-VBA) uses an inner DO loop allowing that also days 29 to 31 are treated right.

It has an optional argument "Multi" which handles the number of correct results to be left out in favor of the next one. This outer loop is simply a recursive function call. See the samples in the commented code at the beginning.

"Multi" later will be extended that weekday and date day can be defined in the function explicitly itself, not implicitly by the first argument. So meanwhile, you might still need Solution 1 in C2.

Solution 4 - A calculating Excel function for all days: (created 2007/05/07)

This function is based on following facts: 

- the combination "desired week- and date-day" repeats after 4, 13, 26, 35, 39, 48, 61, 65, 74 or 87 weeks, coded as '0=FJS`dmz.
- each month has its own characteristics for a successor: 12 months
- the successor varies between days 1-28, 29, 30 and 31: 12 months by 4 cases = 48
- it also varies between normal and leap year: 12 months by 4 cases by 2 years = 96
- Dec 31st is the only date with 3 possible exits instead of 2: 96 + 1 = 97 

By taking the week ("as a function of 7 days"), the 10 possible exits "N of weeks" can be directly converted into 1-digit numbers using the 7bit international section of ASCII, shifted by 35.

Why the difference on Dec 31st? Simply: It looks beyond the next year. The cases are:
- none of the following 2 years is a leap year (65 weeks)
- the first following year is a leap year (13 weeks)
- the second following year is a leap year (87 weeks)

See the correction in the part of the formula, following the last "7".

If C2 is any date having the combination "desired week- and date-day",

=C2+(CODE(MID(IF(DAY(C2)=DAY(C2+(CODE(MID(
"J'F0FF`J0=J=J'F0FJ`J0=J=J'F0FJ`J0=J=J'm0FJmJ0JJd",
MONTH(C2)+MAX(DAY(C2)-28,)*12,1))-35)*7),
"J'F0FF`J0=J=J'F0FJ`J0=J=J'F0FJ`J0=J=J'm0FJmJ0JJd",
"0=F0FFS`0S=00=F0FFS`0S=00=F0F`S`0S=0==`0F`z`0`=z"),
MONTH(C2)+MAX(DAY(C2)-28,)*12,1))-35)*7
-AND(DAY(C2+{0,366})=31)*(MONTH(C2)=12)*518

shows its direct successor.

Note: Dates before March 1, 1900, do not calculate right because Excel assumes a non-existing leap year 1900.