• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
  • 3
  • 2
  • 2
  • +2
1 Solution
 
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
 
Rgonzo1971Commented:
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
 
ProfessorJimJamCommented:
shorter

=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*3)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),8-1))
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
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 HensonIT & Database AssistantCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now