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 yearIf 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! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
