sunday date

[Trevor3007]

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

 

[MARK858]

Always the next Sunday?

 

[James006]

Hello,

You can test following :

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

Hope this will help

 

[MARK858]

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

 

[GR00007]

Another way to get the next Sunday from a date:

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

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

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]MARK858[/TD]
[TD="align: center"]James006[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]17/12/2018[/TD]

[TD="align: center"]23/12/2018[/TD]
[TD="align: center"]23/12/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]23/12/2018[/TD]

[TD="align: center"]23/12/2018[/TD]
[TD="align: center"]30/12/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]24/12/2018[/TD]

[TD="align: center"]30/12/2018[/TD]
[TD="align: center"]30/12/2018[/TD]

</tbody>

Spreadsheet Formulas

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]

...

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

 

[Trevor3007]

Another way to get the next Sunday from a date:

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

thanks Mark857.

that works great :]
KR
Trevor3007

 

[James006]

thanks Mark857

You forgot ... the tip ... MARK858 ....