Converting Regular Dates to Week Ending or Week Beginning Dates in Excel

1. Decide whether you want to use Fridays or Saturdays as your week ending dates, and whether you want to use Sundays or Mondays as your week beginning dates.
2. Create a new column where you will enter the week ending or week beginning dates. Let's say you want to enter the week ending dates in column B and the week beginning dates in column C.
3. In cell B2, enter the following formula:
   Code:

   =A2+7-WEEKDAY(A2,2)

4. Copy the formula in cell B2 and paste it into the rest of the cells in column B that correspond to the dates in column A.
5. In cell C2, enter the following formula:
   Code:

   =A2-WEEKDAY(A2,1)+1

6. Copy the formula in cell C2 and paste it into the rest of the cells in column C that correspond to the dates in column A.

Now you have a column with either week ending or week beginning dates, depending on your preference. You can use these dates to stratify your data by week.