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.
|Here you have a list of birthday for instance, and have you ever tried to calculate the age today based on thses birthday? Kutools for Excel's Calculate age based on birthday can do you a nice favor. Note: The date of "Today" in below example is 1/10/2016|
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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.
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:
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
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:
(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))
Recommended Productivity Tools