Solved

Third Sunday of the Month

Posted on 2016-10-11
10
90 Views
Last Modified: 2016-10-11
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
Comment
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
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
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

Open in new window

Regards
0
 
LVL 52

Accepted Solution

by:
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

Open in new window

0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41838202
shorter

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

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 33

Expert Comment

by:Rob Henson
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

by:pdvsa
ID: 41838293
thank you!! perfect.
0
 
LVL 17

Expert Comment

by:Emmanuel Adebayo
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

by:ProfessorJimJam
ID: 41838436
@Emmanuel Adebayo

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

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

Expert Comment

by:Emmanuel Adebayo
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

by:ProfessorJimJam
ID: 41838753
thanks Emmanuel Adebayo

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question