Weekending Formula

[zanderaruba]

I am trying to create a week ending formula in excel. I have a list of dates and in a new column I would like to display the week ending of these dates. I want the weeks to end on Sunday. For example, if the date column has the date 7/24/12 in it, I would like the week ending formula to spit out 7/29/12. What formula would achieve this? Thanks for the help.

 

[Rick Rothstein]

Assuming you want a Sunday date to not change, give this formula a try...

=A1-WEEKDAY(A1)+1+7*(WEEKDAY(A1)<>1)

 

[BenMiller]

With your date in A1, try =A1+(7-WEEKDAY(A1,2))

 

[zanderaruba]

With your date in A1, try =A1+(7-WEEKDAY(A1,2))

Thanks! Worked perfectly.

 

[Jeff Meyers]

Give this a try:

Sheet4[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD="align: center"]Date[/TD]
[TD="align: center"]Week End[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]07/24/2012[/TD]
[TD="align: right"]07/29/2012[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]07/25/2012[/TD]
[TD="align: right"]07/29/2012[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]07/26/2012[/TD]
[TD="align: right"]07/29/2012[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]07/27/2012[/TD]
[TD="align: right"]07/29/2012[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD="align: right"]07/28/2012[/TD]
[TD="align: right"]07/29/2012[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD="align: right"]07/29/2012[/TD]
[TD="align: right"]07/29/2012[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]08/05/2012[/TD]
[TD="align: right"]08/05/2012[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD="align: right"]08/11/2012[/TD]
[TD="align: right"]08/12/2012[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD="align: right"]08/20/2012[/TD]
[TD="align: right"]08/26/2012[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD="align: right"]09/03/2012[/TD]
[TD="align: right"]09/09/2012[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD="align: right"]09/27/2012[/TD]
[TD="align: right"]09/30/2012[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD="align: right"]10/06/2012[/TD]
[TD="align: right"]10/07/2012[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD="align: right"]10/07/2012[/TD]
[TD="align: right"]10/07/2012[/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD="align: right"]11/01/2012[/TD]
[TD="align: right"]11/04/2012[/TD]
[/TR]
[TR]
[TH]16[/TH]
[TD="align: right"]11/30/2012[/TD]
[TD="align: right"]12/02/2012[/TD]
[/TR]
[TR]
[TH]17[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]18[/TH]
[TD]-------------------[/TD]
[TD]-------------------[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
B2	=A2+7-WEEKDAY(A2,2)
B3	=A3+7-WEEKDAY(A3,2)
B4	=A4+7-WEEKDAY(A4,2)
B5	=A5+7-WEEKDAY(A5,2)
B6	=A6+7-WEEKDAY(A6,2)
B7	=A7+7-WEEKDAY(A7,2)
B8	=A8+7-WEEKDAY(A8,2)
B9	=A9+7-WEEKDAY(A9,2)
B10	=A10+7-WEEKDAY(A10,2)
B11	=A11+7-WEEKDAY(A11,2)
B12	=A12+7-WEEKDAY(A12,2)
B13	=A13+7-WEEKDAY(A13,2)
B14	=A14+7-WEEKDAY(A14,2)
B15	=A15+7-WEEKDAY(A15,2)
B16	=A16+7-WEEKDAY(A16,2)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!