Third Sunday of Everymonth

[Kvamsheedhar]

Pls.. Help!!!

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

Thanks,

Vamshi.

 

[Matty]

Welcome to the Board.

Try:

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))-6

Where A1 contains a date.

Matty

 

[Expiry]

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

=A1+21-WEEKDAY(A1,2)

 

[Matty]

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

 

[Kvamsheedhar]

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

 

[barry houdini]

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

 

[Expiry]

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.

 

[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.

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

 

[Expiry]

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

 

[Rick Rothstein]

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