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

**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

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(83) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

Finding the nearest date in a range of dates using excel formula

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]Comments(42) Filed in category: Dates, Excel

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 […]Comments(31) Filed in category: Excel, Overlapping

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]Comments(24) Filed in category: Excel, Overlapping

Highlight duplicate values and overlapping dates in excel

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]Comments(23) Filed in category: Excel, Overlapping

Count records between two dates with multiple parameters in excel

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]Comments(14) Filed in category: Count values, Dates, Excel

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]Comments(12) Filed in category: Dates, Excel, Sort values

Visualize date ranges in a calendar

Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible […]Comments(10) Filed in category: Calendar, Excel, Overlapping