## How to automatically calculate a specific day of a month

*Article updated on April 27, 2011*

**Question:**

How to calculate every third monday of a month?

### Answer:

**Array formula:**

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- 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

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article