# sunday date

#### Trevor3007

##### Well-known Member
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?

Trevor3007

### 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
Always the next Sunday?

#### James006

##### Well-known Member
Hello,

You can test following :

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

Hope this will help

#### MARK858

##### MrExcel MVP
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

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
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

 A B C D 1 MARK858 James006 2 17/12/2018 23/12/2018 23/12/2018 3 23/12/2018 23/12/2018 30/12/2018 4 24/12/2018 30/12/2018 30/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>

 Cell Formula 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

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

#### Trevor3007

##### Well-known Member
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

#### Trevor3007

##### Well-known Member
thanks Mark857.

that works great :]
KR
Trevor3007

Replies
2
Views
42
Replies
6
Views
71
Replies
7
Views
64
Replies
7
Views
40
Replies
4
Views
107