# Third Sunday of Everymonth

#### Kvamsheedhar

##### New Member
Pls.. Help!!!

I wanna get the easiest method to find the third Sunday of every month !!!

Thanks,

Vamshi.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Welcome to the Board.

Try:

Code:
``=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))-6``

Where A1 contains a date.

Matty

I think this works out ok, where A1 is the 1st of the month.

=A1+21-WEEKDAY(A1,2)

I think this works out ok, where A1 is the 1st of the month.

=A1+21-WEEKDAY(A1,2)
Hi Expiry,

I don't think this will work for all months. Try it with 01/01/2011 or 01/05/2011 for example.

Matty

I think this works out ok, where A1 is the 1st of the month.

=A1+21-WEEKDAY(A1,2)

Thanks Expiry for quick help

However, When I use fill handle, the above mentioned formula doesn't work.. Please suggest..

Thanks..

Can you explain what happens? Do you have a list of 1st of month dates?

Another way is to use a version of that formula to get 3rd Sunday of the current month, i.e. in B2 use this formula

=TODAY()-DAY(TODAY())+22-WEEKDAY(TODAY()-DAY(TODAY()))

then in B3 copied down you can get a list of all the subsequent 3rd Sunday dates by using this formula

=B2+28+(DAY(B2+28)<15)*7

format in required date format

Hi Expiry,

I don't think this will work for all months. Try it with 01/01/2011 or 01/05/2011 for example.

Matty

This works for me, for every one of the 12 months of 2011. It returns

16th Jan, 20th Feb, 20th Mar, 17th Apr, 15th May, 19th Jun, 17th Jul, 21st Aug, 18th Sep, 16th Oct, 20th Nov, 18th Dec.

This works for me, for every one of the 12 months of 2011. It returns

16th Jan, 20th Feb, 20th Mar, 17th Apr, 15th May, 19th Jun, 17th Jul, 21st Aug, 18th Sep, 16th Oct, 20th Nov, 18th Dec.
You're right. I got myself muddled up with this one . For some reason I got it into my head that the last but one Sunday was what the OP was after, when in fact it's simply the third Sunday.

Apologies for misunderstanding, Expiry.

Matty

Yey! This means I'm officially brilliant. Well..... a bit.

Here is a generic formula for finding the Nth such-and-such day of the month...

=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

where Nth is the number you want 1st, 2nd, 3rd etc; and where DoW stands for day of the week with Sunday being 1, Monday being 2 and so on; and where Yr is the Year and MM is the month. Since you wanted the 3rd Sunday, and assuming any date in the month is in A1, that generic formula would become this...

=DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-1))

Simplifying this by doing the indicated math operations, it becomes...

=DATE(YEAR(A1),MONTH(A1),22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),7))

Replies
4
Views
90
Replies
0
Views
69
Replies
5
Views
191
Replies
1
Views
66
Replies
5
Views
213

### Forum statistics

1,203,489
Messages
6,055,722
Members
444,814
Latest member
AutomateDifficulty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back