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
pdvsaProject financeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
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
 
Rgonzo1971Commented:
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
 
ProfessorJimJamCommented:
shorter

=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-1))
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rob HensonFinance 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
 
pdvsaProject financeAuthor Commented:
thank you!! perfect.
0
 
Emmanuel AdebayoGlobal 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
 
ProfessorJimJamCommented:
@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
 
Rob HensonFinance AnalystCommented:
Likewise, don't know why OP went for longer version over my suggestion.
0
 
Emmanuel AdebayoGlobal 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
 
ProfessorJimJamCommented:
thanks Emmanuel Adebayo

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.

All Courses

From novice to tech pro — start learning today.