Non-VBA formula to find 2nd Sunday of a given month

  • Thread starter Thread starter DavidObeid
  • Start date Start date
D

DavidObeid

Can anyone help me write a formula to find the date of the second sunda
in a given month?

Thanks in Advance,

Dav
 
David,

Assuming you have a date in A1, this gives the 2nd Sunday of that date

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi David,

This appears to work and is one method,

=IF(WEEKDAY(DATE(YEAR(J8),MONTH(J8),1))=1, DATE(YEAR(J8),MONTH(J8),1)+7,
DATE(YEAR(J8),MONTH(J8),1)+15-WEEKDAY(DATE(YEAR(J8),MONTH(J8),1)))

J8 is where you have a date (say 27 Nov 2003).

You get an answer of 9 Nov 2003.

If you have J8 equal to 15 Feb 2004, you should get as an answer 8 Feb 2004.

Hope that helps.

Regards,
Kevin
 
Hi Bob,

I like your answer better. :-)

Regards,
Kevin


Bob Phillips said:
David,

Assuming you have a date in A1, this gives the 2nd Sunday of that date

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Thankyou Kevin!

I am ever amazed at how good this forum is in answering questions from
idiots like me.

Regards,

Dave
 
David,

You are most welcome. You should use Bob's solution as it cleaner, more
elegant. Mine is more of a brute force approach. Both solutions work
though.

Speaking on behalf of Bob, you are most welcome for the solutions.

Best regards,
Kevin
 
Dear Kevin,

I tried using the formula you gave me to find a different occurance o
a day in a month (eg the 2nd Monday) and I am having troubles gettin
it to work.

here is what you gave me (modified to fit where data is in m
spreadsheet):

=IF(WEEKDAY(DATE(YEAR($A$2),MONTH(C41),1))=1
DATE(YEAR($A$2),MONTH(C41),1)+7,DATE(YEAR($A$2),MONTH(C41),1)+15-WEEKDAY(DATE(YEAR($A$2),MONTH(C41),1)))

and it works fine!

here is what I tried to get it to find the 2nd monday:

=IF(WEEKDAY(DATE(YEAR($A$2),MONTH(C43),1))=2
DATE(YEAR($A$2),MONTH(C43),1)+7,DATE(YEAR($A$2),MONTH(C43),1)+15-WEEKDAY(DATE(YEAR($A$2),MONTH(C43),1)))

And it is giving me wrong dates.

I can't find my mistake. Can you help?

Thanks in advance,

Dav
 
Can anyone help me write a formula to find the date of the second sunday
in a given month?

Thanks in Advance,

Dave
Click to expand...

I think I have it correct this time <g>.

If the first of the month is in A1, then:

=A1+13-WEEKDAY(A1,3)

If it's a random date in the month in A1, then:

=DATE(YEAR(A1),MONTH(A1),14)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),3)


--ron
 
Hi Dave,

Here is what I got to work....

=IF(WEEKDAY(DATE($A$2,C43,1))<=2,
DATE($A$2,C43,1)+9-WEEKDAY(DATE($A$2,C43,1)),
DATE($A$2,C43,1)+16-WEEKDAY(DATE($A$2,C43,1)))

A2 = 2004
C43= 4 (April)

I get 12 April 04.

I am going to look at Bob's solution and see if I can adapt it. I like
Bob's solution because it was more "generic." But this should work. Check
back in a bit and see if either Bob or I have posted another solution for
you.

Regards,
Kevin
 
David,

I adapted Bob's solution to your "Monday" challenge.

=DATE($A$2,C43,1)+MOD(9-WEEKDAY(DATE($A$2,C43,1)),7)+7

A2= 2004
C43=4

12 April 2004 is the answer.

Let me explain Bob's solution.

The first part of date is easy. It is just the first day of the month.

The Mod portion

If you start with Sunday being 1, the following Monday is 9. Subtract Day
of the Week for the first day. In our example, the first of the month is
Thurs and thus day of week is 5. The modulo of (9-5)/7 is 4. In other
words, 4 is the remainder. This means you need another 4 days to get to the
first Monday. And then add 7 days to get to the second Monday.

Let's look at March 2004.

The first day of the week is Monday, which is a 2. The modulo of (9-2)/7 is
0. In other words, you need 0 days to get to the first Monday. So just add
another 7 days to get to the second Monday.

Bob's solution to me is cleaner and more elegant than my solution and I
encourage you to use Bob's solution.

Hope this helps.

Regards,
Kevin
 
Bob Phillips said:
Assuming you have a date in A1, this gives the 2nd Sunday of that date

=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7
Click to expand...
....

I love 'name that tune' problems. Another alternative,

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1))+15
 
Harlan,

How would you adapt your formula for the second Monday (David's second
request)?

I tried simply increasing the 15 to 16, but I get the wrong answer when I
examine March 2004, using a "seed" (or A1) date of 15 March 2004. I get an
answer of 15 March 2004 (same as the seed, coincidence), but the correct
answer should be 8 March 2004.

TIA.

Regards,
Kevin
 
How about

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)+15


Here are 2 other options (for 1900 date system)

=INT((DATE(YEAR(A1),MONTH(A1),5))/7)*7+9

for all date systems

=DATE(YEAR(A1),MONTH(A1),15)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))
 
Peo,

Thank you very much!

Can I bother you further to inquire as to the logic. I will tell you where
I got to, and then you can correct or add as the case might be?

Let's use 15 March 2004 as an example.

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)+15

A1-DAY(A1) gives the last day of the prior month. 29 Feb 2004.

WEEKDAY(A1-DAY(A1),2)...is the weekday of the last day of the prior month,
using Monday as 1. So in our case, we have Sunday or 7.
Questions:
1) Why did you use the "2" parameter? Guess: you wanted the Monday to be
one, just as for the Sunday example, Sunday was 1.
2) Would the symmetry of this example break down if you wanted the 2nd
Thursday because you cannot set Thursday to 1?

A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)

Yields the 22nd (=29-7) of February. You are always going to "back-up" into
the previous month to a Sunday where you have a full week remaining in prior
month? Now you add 15.


2nd Example:

=DATE(YEAR(A1),MONTH(A1),15)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))

I understand the end result of the equation, though I do not understand why
you started off with the 15th of the current month. So I am missing step 1.
I suspect once I get a slight push, your solution will rapidly make sense.
But right now, I am stumped.

The reason for asking my questions is to better my understanding. Like most
everything else, a bit of practice always helps.

Thank you so much Peo.

Best regards,
Kevin
 
Kevin,
Thank you very much!

Can I bother you further to inquire as to the logic. I will tell you where
I got to, and then you can correct or add as the case might be?

Let's use 15 March 2004 as an example.

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)+15

A1-DAY(A1) gives the last day of the prior month. 29 Feb 2004.

WEEKDAY(A1-DAY(A1),2)...is the weekday of the last day of the prior month,
using Monday as 1. So in our case, we have Sunday or 7.
Questions:
1) Why did you use the "2" parameter? Guess: you wanted the Monday to be
one, just as for the Sunday example, Sunday was 1.
2) Would the symmetry of this example break down if you wanted the 2nd
Thursday because you cannot set Thursday to 1?
Click to expand...

Yes, you would have to offset that.. The reason I added the weekday 2 option
is just that.

=A1-DAY(A1)-WEEKDAY(A1-DAY(A1),3)+15

will obviously return the second Tuesday


A1-DAY(A1)-WEEKDAY(A1-DAY(A1),2)

Yields the 22nd (=29-7) of February. You are always going to "back-up" into
the previous month to a Sunday where you have a full week remaining in prior
month? Now you add 15.


2nd Example:

=DATE(YEAR(A1),MONTH(A1),15)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))

I understand the end result of the equation, though I do not understand why
you started off with the 15th of the current month. So I am missing step 1.
I suspect once I get a slight push, your solution will rapidly make sense.
But right now, I am stumped.
Click to expand...


Ok, the reason it starts with 15 is that the weekday of the 15th of the
month will
always be the third occurrence of that particular weekday, Now if the first
date is a Thursday (April 1st 2004)
how many days differ between Thursday and Monday? 3 days.
Now the second part of the formula depending on what day we use, 6 in this
example
for Mondays will always return the difference between the 15th and the
second Monday.
Weekday for 04/06/04 will return 3, 04/15/04 - 3 is a Monday. Now using
March 2004,
which start with a Monday, that means that 03/15/04 will be the third Monday
of the month
and 03/06/04 is a Saturday, Weekday for Saturday is 7, so 15 - 7 must be the
second Monday.
Since the weekday(date) will always return this internal indexing it will
always be correct.
I believe I picked this up from Daniel Maher who is in my opinion extremely
clever when it comes to dates.
So if you want the second Sunday, change the last day part to 7, 5 for
Tuesday..

The reason for asking my questions is to better my understanding. Like most
everything else, a bit of practice always helps.

Thank you so much Peo.

Best regards,
Kevin
Click to expand...


Regards,

Peo Sjoblom
 
Peo,

Thank you so much for taking considerable time to respond to my question.

Best regards,
Kevin
 
Kevin,

The basic formula gives the first occurrence of Sunday. The second, third
etc. occurrence is simply achieved by adding 7, 14, etc to that formula

When I looked at this, I thought about the day of the week problem, and I
came up with this to get the first day of the month

Sun
=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)
Mon =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),7)),7)
Tue
=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6)),7)
Wed =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),5)),7)
etc.

Problem here is that Help only gives 2nd argument values of 1,2,3. SO
although it seems to work, it may not be supported going forward.

What I decided to do, as this is such a frequent request, is to knock up a
UDF that does it for any occurrence, including last, and any day,.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Kevin Stecyk said:
David,

I adapted Bob's solution to your "Monday" challenge.

=DATE($A$2,C43,1)+MOD(9-WEEKDAY(DATE($A$2,C43,1)),7)+7

A2= 2004
C43=4

12 April 2004 is the answer.

Let me explain Bob's solution.

The first part of date is easy. It is just the first day of the month.

The Mod portion

If you start with Sunday being 1, the following Monday is 9. Subtract Day
of the Week for the first day. In our example, the first of the month is
Thurs and thus day of week is 5. The modulo of (9-5)/7 is 4. In other
words, 4 is the remainder. This means you need another 4 days to get to the
first Monday. And then add 7 days to get to the second Monday.

Let's look at March 2004.

The first day of the week is Monday, which is a 2. The modulo of (9-2)/7 is
0. In other words, you need 0 days to get to the first Monday. So just add
another 7 days to get to the second Monday.

Bob's solution to me is cleaner and more elegant than my solution and I
encourage you to use Bob's solution.

Hope this helps.

Regards,
Kevin




DavidObeid said:
Dear Kevin,

I tried using the formula you gave me to find a different occurance of
a day in a month (eg the 2nd Monday) and I am having troubles getting
it to work.

here is what you gave me (modified to fit where data is in my
spreadsheet):

=IF(WEEKDAY(DATE(YEAR($A$2),MONTH(C41),1))=1,
Click to expand...
DATE(YEAR($A$2),MONTH(C41),1)+7,DATE(YEAR($A$2),MONTH(C41),1)+15-WEEKDAY(DAT
E(YEAR($A$2),MONTH(C41),1)))

and it works fine!

here is what I tried to get it to find the 2nd monday:

=IF(WEEKDAY(DATE(YEAR($A$2),MONTH(C43),1))=2,
Click to expand...
DATE(YEAR($A$2),MONTH(C43),1)+7,DATE(YEAR($A$2),MONTH(C43),1)+15-WEEKDAY(DAT
Click to expand...
 
Hi Bob,

Thank you very much for your assistance! I am saving your post in my
personal archive.

Best regards,
Kevin


Bob Phillips said:
Kevin,

The basic formula gives the first occurrence of Sunday. The second, third
etc. occurrence is simply achieved by adding 7, 14, etc to that formula

When I looked at this, I thought about the day of the week problem, and I
came up with this to get the first day of the month

Sun
=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)
Mon =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),7)),7)
Tue
=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6)),7)
Wed
Click to expand...
=DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),5)),7)
etc.

Problem here is that Help only gives 2nd argument values of 1,2,3. SO
although it seems to work, it may not be supported going forward.

What I decided to do, as this is such a frequent request, is to knock up a
UDF that does it for any occurrence, including last, and any day,.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Kevin Stecyk said:
David,

I adapted Bob's solution to your "Monday" challenge.

=DATE($A$2,C43,1)+MOD(9-WEEKDAY(DATE($A$2,C43,1)),7)+7

A2= 2004
C43=4

12 April 2004 is the answer.

Let me explain Bob's solution.

The first part of date is easy. It is just the first day of the month.

The Mod portion

If you start with Sunday being 1, the following Monday is 9. Subtract Day
of the Week for the first day. In our example, the first of the month is
Thurs and thus day of week is 5. The modulo of (9-5)/7 is 4. In other
words, 4 is the remainder. This means you need another 4 days to get to the
first Monday. And then add 7 days to get to the second Monday.

Let's look at March 2004.

The first day of the week is Monday, which is a 2. The modulo of
Click to expand...
(9-2)/7
is
0. In other words, you need 0 days to get to the first Monday. So just add
another 7 days to get to the second Monday.

Bob's solution to me is cleaner and more elegant than my solution and I
encourage you to use Bob's solution.

Hope this helps.

Regards,
Kevin
Click to expand...
DATE(YEAR($A$2),MONTH(C41),1)+7,DATE(YEAR($A$2),MONTH(C41),1)+15-WEEKDAY(DATDATE(YEAR($A$2),MONTH(C43),1)+7,DATE(YEAR($A$2),MONTH(C43),1)+15-WEEKDAY(DAT
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting calendar Days - not including Sundays 3
Excel VBA 0
Formula to get 1st,2nd,3rd,4th,5th Sundays from month end date. 5
Calculating dates to include Saturdays but not Sundays 1
Can I get no of Sundays and working days of the month? 6
Finding 3rd (or 2nd) Sunday in a given year/month 5
Reference Formula Help 1
Quarterly Formula 1

Back
Top