Table of Contents
You can use the following formulas to get the last day of the week for a date in Excel:
Formula 1: Get Last Day of Week (Assuming Last Day is Saturday)
=A2-WEEKDAY(A2)+7
Formula 2: Get Last Day of Week (Assuming Last Day is Sunday)
=A2-WEEKDAY(A2,2)+7
Both formulas assume that cell A2 contains the date you’d like to find the last day of the week for.
The following example shows how to use each formula in practice with the following column of dates in Excel:
Example: How to Get Last Day of Week in Excel
We can type the following formulas into cell B2 and C2 to get the first day of the week for the date in cell A2:
- B2: =A2-WEEKDAY(A2)+7
- C2: =A2-WEEKDAY(A2,2)+7
We can then click and drag each of these formulas down to the remaining cells in columns B and C:
Note: If the dates are shown as numeric values instead, then we can highlight the cell range B2:C11 and then click the Number Format dropdown menu on the Home tab and then click Short Date.
Each of the numeric values in columns Band C will be converted to date formats.
Column B shows the last day of the week that the date in column A falls in, assuming the last day is considered Saturday.
Column C shows the last day of the week that the date in column A falls in, assuming the last day is considered Sunday.
Thus, if we consider Saturday the last day of the week then that date is 1/14/2022.
Or if we consider Sunday the last day of the week then that date is 1/15/2022.
These dates match the ones calculated by our formulas.