Question:

How to calculate every third monday of  a month?

Answer:

Array formula:

How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

Explaining array formula

Step 1 - Create an array of dates

=SMALL(IF(WEEKDAY(A2+ROW($1:$31)-1)=2,A2+ROW($1:$31)-1,""),3)

A2+ROW($1:$31)-1

becomes

39814 + {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31} -1

becomes

39814 + {0,1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30}

and returns

{39814, 39815, 39816, 39817, 39818, 39819, 39820, 39821, 39822, 39823, 39824, 39825, 39826, 39827, 39828, 39829, 39830, 39831, 39832, 39833, 39834, 39835, 39836, 39837, 39838, 39839, 39840, 39841, 39842, 39843, 39844}

Step 2 - Calculate the day of the week of a date

=SMALL(IF(WEEKDAY(A2+ROW($1:$31)-1)=2,A2+ROW($1:$31)-1,""),3)

WEEKDAY(A2+ROW($1:$31)-1)

becomes

WEEKDAY({39814, 39815, 39816, 39817, 39818, 39819, 39820, 39821, 39822, 39823, 39824, 39825, 39826, 39827, 39828, 39829, 39830, 39831, 39832, 39833, 39834, 39835, 39836, 39837, 39838, 39839, 39840, 39841, 39842, 39843, 39844})

and returns

{5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7}

Step 3 - Filter mondays

=SMALL(IF(WEEKDAY(A2+ROW($1:$31)-1)=2,A2+ROW($1:$31)-1,""),3)

IF(WEEKDAY(A2+ROW($1:$31)-1)=2,A2+ROW($1:$31)-1,"")

becomes

IF({5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7;1;2;3;4;5;6;7}=2,A2+ROW($1:$31)-1,"")

becomes

IF({FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE},A2+ROW($1:$31)-1,"")

becomes

IF({FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE},{39814, 39815, 39816, 39817, 39818, 39819, 39820, 39821, 39822, 39823, 39824, 39825, 39826, 39827, 39828, 39829, 39830, 39831, 39832, 39833, 39834, 39835, 39836, 39837, 39838, 39839, 39840, 39841, 39842, 39843, 39844},"")

and returns

{"","","","",39818,"","","","","","",39825,"","","","","","",39832,"","","","","","",39839,"","","","",""}

Step 3 - Filter third monday

=SMALL(IF(WEEKDAY(A2+ROW($1:$31)-1)=2,A2+ROW($1:$31)-1,""),3)

becomes

=SMALL({"","","","",39818,"","","","","","",39825,"","","","","","",39832,"","","","","","",39839,"","","","",""}, 3)

and returns

39832 (2009-01-19)

Download excel sample file for this tutorial

how-to-calculate-date_2.xls
(Excel 97-2003 Workbook *.xls)

Functions in this tutorial:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

WEEKDAY(serialnumber,[return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference