CODEWITHSUNDEEP
exceldateweekday
Nick Knauer
Nick Knauer

Reputation: 4243

Insert week column in Excel

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?

Upvotes: 0

Views: 533

Answers (1)

Jerry
Jerry

Reputation: 71558

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

enter image description here

Upvotes: 3

Related Questions