Formula to find a "week ending" Sunday Date

[sunday_storyteller]

I need help with a formula. I have a certain list of dates, by day...and I need them listed by "week ending" (where a week "ends" on a Sunday).

For example, for the week ending 5/23, I might have the dates:
5/23/10 (Sunday) - the formula should pull 5/23/10
5/22/10 (Saturday) - the formula should pull 5/23/10

And if the date was earlier...say 5/11/10...it should return "5/16/10" because that is the Sunday of the week that contained 5/11/10.

I know there is a way to do this with the Weekdays function combined in a formula, but I don't remember what it is. Does anyone know?

Thanks!

SundayST

 

[ccordner]

Is this a formula in a cell, or in a macro?

 

[lenze]

=TODAY()+(7-WEEKDAY(TODAY(),2))

Or

=A1+(7-WEEKDAY(A1,2))

lenze

 

[VaraK]

Hello,

I'm assuming your date is in cell E3 for which you are looking to get the sunday for that week.
Here is the formula that does the trick for you:

=(7-WEEKDAY(E3,2))+E3

Pls let me know if this helps.

 

[VaraK]

Wow! Didn't see your post Lenze.

 

[dcofer]

This formula appears to work when I tested it. The cell in which the formula below is posted will keep the same date as in A1 if the date in A1 is a Sunday. But it will give you the following Sunday if the date is not a Sunday.

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

Hope this helps.

---dave

 

[pogba1113]

Hello,

I'm assuming your date is in cell E3 for which you are looking to get the sunday for that week.
Here is the formula that does the trick for you:

=(7-WEEKDAY(E3,2))+E3

Pls let me know if this helps.

Thank you so much, I have been looking for this formula for over a day

 

[Scott R]

I'm starting to use this function more:
=WORKDAY.INTL(A1 - 1, 1, "1111110")