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.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
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))
Add Days, Years, Months, Hours, Minutes, and Seconds to Dates in Excel with Ease |
If you have a date in a cell and need to add days, years, months, hours, minutes, or seconds, using formulas can be complicated and hard to remember. With Kutools for Excel’s Date & Time Helper tool, you can effortlessly add time units to a date, calculate date differences, or even determine someone's age based on their birthdate - all without needing to memorize complex formulas. |
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now |
Best Office Productivity Tools
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in