Insert week column in Excel

Question

I have a column in excel that has the day.

I want to add another column that will extract the week of that day (Mon-Sun) and display it as week ending on that Sunday's date.

     Day        
4/20/2019   
4/21/2019   
4/22/2019   
4/23/2019   
4/24/2019   
4/25/2019   

Expected Output

      Day        Week
4/20/2019   4/21/2019
4/21/2019   4/21/2019
4/22/2019   4/28/2019
4/23/2019   4/28/2019
4/24/2019   4/28/2019
4/25/2019   4/28/2019

Does anyone know how to add this column using excel functions?

Answer 1

WEEKDAY with the second parameter as 2 gives a number between 1 to 7 (7 being Sunday) for the day of the week. You can add to the date, the amount of days till that number becomes 7, in other words:

=A2+(7-WEEKDAY(A2,2))