Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
Register  \/ 


How to calculate week ending date of the given dates/years in Excel?

In some cases, you may want to calculate the week ending date of the given dates for better tracking and supervising working missions. In this article, I introduce some tricks which can quickly calculate the week ending date of the give dates, or list all week ending dates of the given year in Excel.

Calculate week ending date of the date

List all week ending dates of the given year

arrow blue right bubble Calculate week ending date of the date

Below formula can help you to calculate the week ending date of the given date.

For instance, here we set Saturday as the ending date of a week, and please do as below:

Select a blank cell next to the date you want to calculate its week ending date, B6 for instance, and enter this formula =A6+7-WEEKDAY(A6), press Enter key, drag the fill handle down to the cells needed this formula. See screenshot:
doc week ending date 1

Tip: If you like to use Friday as week ending date, you just need to change 7 into 6 in the formula, if you like to use Sunday, change 7 into 8, and so on.

arrow blue right bubble List all week ending dates of the given year

If you want to list all week ending dates of a given year, for instance, to list all Saturdays of 2016, pleas do as below steps:

1. Select a blank cell, A2, and enter the year whose week ending dates you want to list. See screenshot:
doc week ending date 2

2. In the cell B2, enter this formula =DATE(A2,1,1)+7-WEEKDAY(DATE(A2,1,1)), A2 is the year you want to use, press Enter key, you can see the first week ending date of the year has been displayed. See screenshot:
doc week ending date 3

3. Go to C2, and enter this formula =IF(YEAR(B2+7)=$A$2,B2+7,""), and then drag autofill handle right to the cells to list all week ending dates of the year. See screenshot:
doc week ending date 4


(1) The formula =IF(YEAR(B2+7)=$A$2,B2+7,"") will return 5-digits number instead of date. Please change the 5-digits number to date with selecting and clicking Home > Number Format > Short Date.

(2) If you want to list all Fridays of the year, change formula to =DATE(A2,1,1)+7-WEEKDAY(DATE(A2,1,2))

Easily add days/years/month/hours/minutes/seconds to a datetime in Excel

Supposing you have a date time format data in a cell, and now you need to add a number of days, years, months, hours, minutes, or seconds to this date. Normally, using formula is the first method for all Excel users, but it’s hard to remember all formulas. With Kutools for Excel’s Date & Time Helper utility, you can easily add days, years, months, or hours, minutes or seconds to a date time, moreover, you can caculate the date difference, or the age based on a given birthday without remembering the formula at all. Click for full-featured free trial in 30 days!
doc add hour minute second
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Guest · 1 years ago
    This formula is incorrect, it works fine until you get to the last day of the week and then it gives the end of the following week as the result.

    I'm not yet sure on a fix but have seen two versions of this basic formula on different threads and neither calculate correctly.
    • To post as a guest, your comment is unpublished.
      Dympna Griffin · 1 years ago
      I played around with the formula and found this might answer the problem =Cell reference+7-WEEKDAY(Cell reference-1)
      Example - date I need end of week calculated on is in Cell reference A4 -my formula is therefore = A4+7-WEEKDAY(A4-1).
  • To post as a guest, your comment is unpublished.
    sara · 1 years ago
    How do I get week start - end of week on one excel cell
    I.e. 06 Jan - 12 Jan and so on to make a list of working weeks

  • To post as a guest, your comment is unpublished.
    gjdavis123 · 2 years ago
    Thanks for the formula. I have a Timesheet where I want the week ending date for Monday through Sunday to calculate as Friday's date of that week. I edited the formula as follows:

    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Sorry, Jeff Davis, I do not understand your question, can you take some examples for explain?