Ask a new question

Week ending formula...

If I had Monday's date in a cell 11/03/2013 and I wanted the cell below to automatically populate the week ending date 17/03/2013 (Sunday) what formula would I use? Or combination of formulas?

Hi,

 

If it will always be Mondays date you can use

 

=A1+6

 

or if you want the next Sunday of any date. Note if A1 contains a Sunday date this returns the same date

 

 

=A1+7-WEEKDAY(A1+6)

If this response answers your question then please mark as answer.

Mike H

16 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Whenever this problem comes up, people never seem to offer up what result they want if the original date is a Sunday as well.

If you want Sunday to roll-up to the next Sunday then,

=A1+(8-WEEKDAY(A1))

If you want Sunday to stay the same then,

=A1+(8-WEEKDAY(A1))*(WEEKDAY(A1)>1)

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Whenever this problem comes up, people never seem to offer up what result they want if the original date is a Sunday as well.

If you want Sunday to roll-up to the next Sunday then,

=A1+(8-WEEKDAY(A1))

If you want Sunday to stay the same then,

=A1+(8-WEEKDAY(A1))*(WEEKDAY(A1)>1)


=A1+(8-WEEKDAY(A1))*(WEEKDAY(A1)>1)

 
'// Here's just another option for same Sunday

=A1+MOD(14,WEEKDAY(A1)+6)

 

 

 

 

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

=A1+(8-WEEKDAY(A1))

This the formula I was looking for I think. So as I understand this formula, can you explain the parts? e.g. What is the 8 for?

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Whenever this problem comes up, people never seem to offer up what result they want if the original date is a Sunday as well.

If you want Sunday to stay the same then,

=A1+(8-WEEKDAY(A1))*(WEEKDAY(A1)>1)


or perhaps (for Sunday to stay the same):  =A1+7-WEEKDAY(A1-1)
Ron

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You have your date in A1 and have to add a number of days to it to reach the next Sunday. Excel treats each day as 1 for every day past Dec 31, 1899. Today happens to be 41,346 and next Sunday is 41,350. The WEEKDAY() function in its default form returns a number representing the day-of-the-week with Sunday starting as 1 to Saturday as 7. The 8 in the formula is the number required to mathematically take the WEEKDAY() and produce a number which will equal Sunday when added to the original date.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

=CEILING(A1-1,7)+1

Regards,
Bernd
http://sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_Forums/excel_forums.html

6 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I like that, except it only works for the 1900 date system.
Ron

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I like that, except it only works for the 1900 date system.
Hi Ron,

For those who like the 1904 date system I can offer #7 of my Excel Don'ts:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd
http://sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_Forums/excel_forums.html

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated February 6, 2025 Views 31,052 Applies to: