# Third Sunday of the Month

Hello,

I would like to return the 3rd Sunday of the month.
I do not have a helper cell and therefore would need to have Date() in the formula and not ref a cell.

thank you
###### Who is Participating?

x

Commented:
or maybe
``````=EOMONTH(TODAY(),-1)+1+1-WEEKDAY(EOMONTH(TODAY(),-1)+1)+(3-(1>=WEEKDAY(EOMONTH(TODAY(),-1)+1)))*7
``````
0

Commented:
Hi,

pls try
``````=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))+(3-(1>=WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))))*7
``````
Regards
0

Commented:
shorter

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

Finance AnalystCommented:
Even shorter:

=CEILING(EOMONTH(TODAY(),-1)+1,7)+15

EOMONTH(TODAY(),-1)+1   returns first day of the month

CEILING(Date,7)  rounds a date up to the following Saturday.  Adding 1 would give first Sunday therefore adding 15 gives third Sunday.
0

Project financeAuthor Commented:
thank you!! perfect.
0

Global Windows Infrastructure Engineer - ConsultantCommented:
You can you the, DATE, MONTH WEEKDAY functionalities in Excel

Put the starting date in cell A1 and enter the fomular below into cell B1

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

If you have 01/10/16 in cell A1, then in Cell B1 you will have 16/10/16

You can copy the formula for all other months.

Regards
0

Commented:

although, the OP has already found a solution and accepted Rgonzo's answer.

i am wondering how the solution you posted is different than the one i provided. :)

noting that OP mentioned not using helper cell.
0

Finance AnalystCommented:
Likewise, don't know why OP went for longer version over my suggestion.
0

Global Windows Infrastructure Engineer - ConsultantCommented:
@ProfessorJimJam, I opened the question when no one has responded and I was sidetracked by another assignment, so by the time I responded many experts has contributed but I did not check the page not until I clicked on submit, never mind, he has awarded the score already.

Regards
0

Commented:

it happens me to as well in the past. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.