Finding the next dayof the week after a certain date.

[Kevin Mulvaney]

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)

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

Does this formula give you what you want...

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

Rick

 

[akphidelt]

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.

 

[T. Valko]

One way:

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