Solved

# Third Sunday of the Month

Posted on 2016-10-11
84 Views
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
0
Question by:pdvsa
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +2

LVL 51

Expert Comment

ID: 41838181
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

LVL 51

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 41838188
or maybe
``````=EOMONTH(TODAY(),-1)+1+1-WEEKDAY(EOMONTH(TODAY(),-1)+1)+(3-(1>=WEEKDAY(EOMONTH(TODAY(),-1)+1)))*7
``````
0

LVL 26

Expert Comment

ID: 41838202
shorter

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

LVL 33

Expert Comment

ID: 41838247
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

Author Closing Comment

ID: 41838293
thank you!! perfect.
0

LVL 17

Expert Comment

ID: 41838402
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

LVL 26

Expert Comment

ID: 41838436

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

LVL 33

Expert Comment

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

LVL 17

Expert Comment

ID: 41838672
@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

LVL 26

Expert Comment

ID: 41838753

it happens me to as well in the past. :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article youâ€™ll learn how to use ExcelToWord! to copy data,charts, shapes â€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month5 days, 10 hours left to enroll