Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Calculating Week-Ending Dates.
Written by Allen Wyatt (last updated February 16, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
Do you keep track of information based on week-ending dates? Many businesses do, and therefore need a quick way to calculate the week-ending dates for the complete year. The dates could be easily calculated with a macro, but you can do it just as easily with formulas.
There are two formulas you can use in order to calculate your week-ending dates. Let's assume, for the sake of this example, that your year is stored in cell A1. You could then figure out the first Saturday of the year by using this formula in cell A3:
=DATE(A1,1,1)+7-WEEKDAY(DATE(A1,1,1))
This works because the WEEKDAY function returns a value of 1 (Sunday) through 7 (Saturday) for any date. If you subtract that value from 7, then you have a value of 6 (Sunday) through 0 (Saturday). When you add that value to the DATE value for January 1 of the year, you end up with the first Saturday of the year.
If you prefer to have your weeks end on Fridays, then the formula needs to change a bit:
=DATE(A1,1,1)+7-(WEEKDAY(DATE(A1,1,1)+1))
Finally, if you prefer to have your weeks end on Sundays, then the formula needs to be like this one:
=DATE(A1,1,1)+7-WEEKDAY(DATE(A1,1,1),2)
This formula uses a parameter for the WEEKDAY function that calculates weekdays that range from 1 (Monday) through 7 (Sunday).
Once you have the first week-ending date for the year (in A3, remember?), then you can calculate the rest of the week-ending dates for the year. Place the following formula in cell A4:
=IF(YEAR(A3+7)=$A$1,A3+7,"")
This checks to see if one week past the previous date is still in the year. If it is, then the new date is returned. If it isn't, then an empty string is returned. If you copy this formula from A4 down through A55, then you will have all the desired week-ending dates for the year. With the formulas in place, simply change the year in cell A1 to see how the dates change.
The range A3:A55 provides room for 53 week-ending dates, which is possible for any given year. Because you used the IF statement in the formula in cells A4:A55, then the very last value (A55) will be blank if there were only 52 week-ending dates for the year.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2444) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Calculating Week-Ending Dates.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When you have a huge amount of daily data to analyze, you may want to calculate an average of values for any given date ...
Discover MoreMany businesses need to know when the last business day of the month occurs. This tip discusses several ways you can ...
Discover MoreIt is no secret that Excel allows you to work with dates in your worksheets. Getting your information into a format that ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-08-25 11:59:30
Ruvimbo
Amazing thanks so much! For anyone who got a 5 digit number instead of a date on the last formula i.e. (=IF(YEAR(A3+7)=$A$1,A3+7,"")
All you have to do is right click on the cell; click on 'format cells'; under the 'Number' tab click on 'Date'; then click on 'Okay'. This should then convert the digits into a date.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments