Finding the next dayof the week after a certain date.

  • Thread starter Thread starter Kevin Mulvaney
  • Start date Start 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)
 
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)
Click to expand...

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