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

On Thu, 21 Jun 2012 21:15:34 +0000, Keyrookie 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?

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?

On Fri, 22 Jun 2012 11:21:00 +0000, Keyrookie 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))

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...

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.