Find next Sunday

Keyrookie

unread,

Jun 21, 2012, 5:15:34 PM6/21/12

to

Greetings all,

I'm looking for the formula to locate next Sunday's date. I'm using
this formula now ...
=DATE(YEAR($A$1),MONTH($A$1),1+7*1)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),8-1))
.. to locate the 1st Sunday of the month and then I have 4 cells that
will locate the following Sunday's of the month.

I'm wanting to simplify the process and not have multiple fomulas.

Thanks for your help.

K

--
Keyrookie

Ron Rosenfeld

unread,

Jun 21, 2012, 11:11:28 PM6/21/12

to

"Next" Sunday is given by the formula: =A1+8-WEEKDAY(A1)

The First Sunday of the Month (of the date in A1) is given by the formula:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1))

Keyrookie

unread,

Jun 22, 2012, 7:21:00 AM6/22/12

to

'Ron Rosenfeld[_2_ Wrote:
> ;1602994']On Thu, 21 Jun 2012 21:15:34 +0000, Keyrookie
> <Keyrooki...@excelbanter.com> wrote:
> -

> >
> >Greetings all,
> >
> >I'm looking for the formula to locate next Sunday's date. I'm using
> >this formula now ...
> >=DATE(YEAR($A$1),MONTH($A$1),1+7*1)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),8-1))
> >.. to locate the 1st Sunday of the month and then I have 4 cells that
> >will locate the following Sunday's of the month.
> >
> >I'm wanting to simplify the process and not have multiple fomulas.
> >
> >Thanks for your help.
> >

> >K-

> "Next" Sunday is given by the formula: =A1+8-WEEKDAY(A1)
>
> The First Sunday of the Month (of the date in A1) is given by the
> formula:
>
> =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1))

Thank you, Ron. This formula works great!

However, after testing this for my use I realized I need to show the
current Sunday before I show the next Sunday. In other words, if today
is Sunday, I need to show the current date. Then on Monday I need to
show the next Sunday's date.

Is that possible?

--
Keyrookie

James Ravenswood

unread,

Jun 22, 2012, 10:49:16 AM6/22/12

to

Something like:

=IF(WEEKDAY(TODAY())=1,TODAY(),TODAY()+8-WEEKDAY(TODAY()))

Ron Rosenfeld

unread,

Jun 22, 2012, 6:53:29 PM6/22/12

to

On Fri, 22 Jun 2012 11:21:00 +0000, Keyrookie <Keyrooki...@excelbanter.com> wrote:

>Thank you, Ron. This formula works great!
>
>However, after testing this for my use I realized I need to show the
>current Sunday before I show the next Sunday. In other words, if today
>is Sunday, I need to show the current date. Then on Monday I need to
>show the next Sunday's date.
>
>Is that possible?

Minor change:

Show today if today is Sunday, else show NEXT Sunday:

=A1+7-WEEKDAY(A1-1)

First Sunday of the month of the date in A1 formula remains the same.

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1))

Keyrookie

unread,

Jun 22, 2012, 4:51:35 PM6/22/12

to

James Ravenswood;1603037 Wrote:
> Something like:
>
> =IF(WEEKDAY(TODAY())=1,TODAY(),TODAY()+8-WEEKDAY(TODAY()))

Thanks James. but I couldn't get it to work. I tried putting A1 in the
() behind the TODAY's in the formula and I got an error message. Am I
missing something?

--
Keyrookie

Stan Brown

unread,

Jun 23, 2012, 9:52:18 AM6/23/12

to

On Thu, 21 Jun 2012 21:15:34 +0000, Keyrookie wrote:
>
> I'm looking for the formula to locate next Sunday's date. I'm
> using this formula now ...
> =DATE(YEAR($A$1),MONTH($A$1),1+7*1)-WEEKDAY(DATE(YEAR($A$1),MONTH($A$1),8-1))
> .. to locate the 1st Sunday of the month and then I have 4 cells that
> will locate the following Sunday's of the month.
>
> I'm wanting to simplify the process and not have multiple fomulas.

If you really mean next Sunday, i.e. the Sunday next after today,
then your formula points the way:

=TODAY() + 8-WEEKDAY( TODAY() )

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

James Ravenswood

unread,

Jun 23, 2012, 11:23:26 AM6/23/12

to

My equation does not depend on A1. It calculates the date of the next Sunday from today. If today happends to be a Sunday, it gives today's date.