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

#### 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:

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.

#### 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:

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:

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:

Tips:

(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!

### Best Office Productivity Tools

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ... Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

#### 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!
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hello!
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).
This comment was minimized by the moderator on the site
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

Thanks
This comment was minimized by the moderator on the site
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:

=IF(C2<>"Sun",B2+6-WEEKDAY(B2),B1+6-WEEKDAY(B1))
This comment was minimized by the moderator on the site
Sorry, Jeff Davis, I do not understand your question, can you take some examples for explain?
There are no comments posted here yet