Is there a way to return the date of a particular day after a date, i.e. my
work month always starts on the 26th, but the week ends on sunday. I would
like to enter the first day of the month (the 26th), and automaticaly
generate the week start and end dates (mon-sun). I need a formula to return
the date for the first sunday after the 26th.

ex: week 1 - 6/26/08(thu) - 6/29/08(sun)
week 2 - 6/30/08(mon) - 7/6/08(sun)

On Thu, 21 Aug 2008 09:13:01 -0700, Kevin Mulvaney
wrote:

Is there a way to return the date of a particular day after a date, i.e. my
work month always starts on the 26th, but the week ends on sunday. I would
like to enter the first day of the month (the 26th), and automaticaly
generate the week start and end dates (mon-sun). I need a formula to return
the date for the first sunday after the 26th.

ex: week 1 - 6/26/08(thu) - 6/29/08(sun)
week 2 - 6/30/08(mon) - 7/6/08(sun)

I'm not sure of all your rules, but

=A1+7-WEEKDAY(A1+6)

will return the first Sunday on or after the date in A1
(If A1 is a Sunday, it will return the same date)


=A1+14-WEEKDAY(A1+6)

will return the first Sunday after the date in A1
(If A1 is a Sunday, it will return the following Sunday).
--ron

Does this formula give you what you want...

=A1+MOD(8-WEEKDAY(A1),7)

Rick


"Kevin Mulvaney" wrote in message
...
Is there a way to return the date of a particular day after a date, i.e.
my
work month always starts on the 26th, but the week ends on sunday. I would
like to enter the first day of the month (the 26th), and automaticaly
generate the week start and end dates (mon-sun). I need a formula to
return
the date for the first sunday after the 26th.

ex: week 1 - 6/26/08(thu) - 6/29/08(sun)
week 2 - 6/30/08(mon) - 7/6/08(sun)

Alright, here is some way to do it. Might not be the most efective, but it
should work

=IF(WEEKDAY(A1)=1,A1+6,A1+8-WEEKDAY(A1))

So if the start date is in A1 and you put this formula in B1 it would work.
Otherwise you will have to change the A1's to whatever cell your data starts
in.

"Kevin Mulvaney" wrote:

Is there a way to return the date of a particular day after a date, i.e. my
work month always starts on the 26th, but the week ends on sunday. I would
like to enter the first day of the month (the 26th), and automaticaly
generate the week start and end dates (mon-sun). I need a formula to return
the date for the first sunday after the 26th.

ex: week 1 - 6/26/08(thu) - 6/29/08(sun)
week 2 - 6/30/08(mon) - 7/6/08(sun)

One way:

=A1+CHOOSE(WEEKDAY(A1),7,6,5,4,3,2,1)

--
Biff
Microsoft Excel MVP


"Kevin Mulvaney" wrote in message
...
Is there a way to return the date of a particular day after a date, i.e.
my
work month always starts on the 26th, but the week ends on sunday. I would
like to enter the first day of the month (the 26th), and automaticaly
generate the week start and end dates (mon-sun). I need a formula to
return
the date for the first sunday after the 26th.

ex: week 1 - 6/26/08(thu) - 6/29/08(sun)
week 2 - 6/30/08(mon) - 7/6/08(sun)