Finding the next dayof the week after a certain date.


K

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)
 
Ad

Advertisements

R

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
 
R

Rick Rothstein \(MVP - VB\)

Does this formula give you what you want...

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

Rick
 
A

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

Advertisements


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

Top