Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
hi,

Is there a formula that if I put a date in a cell a1 , b1 will then return that weeks sunday date? Example:-

a1 - 17/12/18 b1 = 23/12/18?

many thanks in advance.
Trevor3007
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Always the next Sunday?
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You can test following :

Code:
=A1+7-mod(a1-1,7)

Hope this will help
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Depending what you want if the date is already a Sunday use James006 formula or the one in column C below (assuming that you do mean the next Sunday and not the nearest Sunday or the Sunday of that week as most conventions start the week on a Sunday so you would want the previous Sunday)...

Excel Workbook
ABCD
1MARK858James006
217/12/201823/12/201823/12/2018
323/12/201823/12/201830/12/2018
424/12/201830/12/201830/12/2018
Sheet1
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184

ADVERTISEMENT

Another way to get the next Sunday from a date:
Code:
=A1 + (6 - WEEKDAY(A1,3))

WEEKDAY with a type of 3 will return 0 thru 6 with 0=Monday.
This will take the date in A1 and add (6 - digit returned by WEEKDAY)
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
Depending what you want if the date is already a Sunday use James006 formula or the one in column C below (assuming that you do mean the next Sunday and not the nearest Sunday or the Sunday of that week as most conventions start the week on a Sunday so you would want the previous Sunday)...

Sheet1

ABCD
1 MARK858James006
217/12/2018 23/12/201823/12/2018
323/12/2018 23/12/201830/12/2018
424/12/2018 30/12/201830/12/2018

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:64px;"><col style="width:75px;"><col style="width:75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C2=A2-WEEKDAY(A2-1)+7
D2=A2+7-MOD(A2-1,7)
C3=A3-WEEKDAY(A3-1)+7
D3=A3+7-MOD(A3-1,7)
C4=A4-WEEKDAY(A4-1)+7
D4=A4+7-MOD(A4-1,7)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


thank you very much for your help Mark858,

I tweaked and used =B3-WEEKDAY(B3-1)+7, which is in cell e3, which works.

However when b3 is empty\blank e3 returns #num ! is it possible that so if b3 is empty\blank that e3 will also be blank\empty?

MTIA
Trevor3007
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
=A2-WEEKDAY(A2-1)+7

<tbody>
</tbody>
</body>
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

...
=IF(B3="","",B3-WEEKDAY(B3-1)+7)
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
Another way to get the next Sunday from a date:
Code:
=A1 + (6 - WEEKDAY(A1,3))

WEEKDAY with a type of 3 will return 0 thru 6 with 0=Monday.
This will take the date in A1 and add (6 - digit returned by WEEKDAY)

hello GR00007

thank you...yes that does work.

hope you have a great xmas.
KR
Trevor3007
 

Watch MrExcel Video

Forum statistics

Threads
1,109,450
Messages
5,528,818
Members
409,838
Latest member
Cowells01
Top