Loading

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.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

ExcelTips Resources

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** 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),

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.

*Related Tips:*

**Save Time and Supercharge Excel!** Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out *Excel 2010 VBA and Macros* today!

=ROUNDUP((TODAY()+d1)/7,0)*7+d2

d1 = gives ability to offset what day the calculation will change to next week. Can be positive or negative

d2 = changes what day of the week the calculation finish with. Can be positive or negative

AKA:

=ROUNDUP((TODAY()-1)/7,0)*7+1

Will return next Sundays and will toggle on Monday at midnight

d1 = gives ability to offset what day the calculation will change to next week. Can be positive or negative

d2 = changes what day of the week the calculation finish with. Can be positive or negative

AKA:

=ROUNDUP((TODAY()-1)/7,0)*7+1

Will return next Sundays and will toggle on Monday at midnight

Thank you, This saved me a shedload of time.

Sorry, I just read that and saw my huge mistake. I wanted to do this:

=TODAY()+7-WEEKDAY(TODAY())

=TODAY()+7-WEEKDAY(TODAY())

Or you could just do the following to get the End of the Week for your current week.

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))+7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

@pei pei,

* In cell A1 type: 04/01/2016 - 08/01/2016

(WITHOUT the double quotes)

* In cell A2 type the following formula and copy down:

=TEXT(LEFT(A1,10)+7,"dd/mm/yyyy")&" - "&TEXT(RIGHT(A1,10)+7,"dd/mm/yyyy")

------------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

* In cell A1 type: 04/01/2016 - 08/01/2016

(WITHOUT the double quotes)

* In cell A2 type the following formula and copy down:

=TEXT(LEFT(A1,10)+7,"dd/mm/yyyy")&" - "&TEXT(RIGHT(A1,10)+7,"dd/mm/yyyy")

------------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

@ganesh,

Press CTRL+1 > Number format > Customize and add a new format - like:

-------------

dd-mmm-yyyy

-------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

Press CTRL+1 > Number format > Customize and add a new format - like:

-------------

dd-mmm-yyyy

-------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2016)

ISRAEL

sir, i want to convert 13/5/2015 to 13-May-2015 kindly help me anybody plz.

Hi is it possible to add 7 days to this format of date "04/01/2016 - 08/01/2016" continuously?

e.g "04/01/2016 - 08/01/2016"

"11/01/2016 - 15/01/2016"

"18/01/2016 - 22/01/2016"

e.g "04/01/2016 - 08/01/2016"

"11/01/2016 - 15/01/2016"

"18/01/2016 - 22/01/2016"

@Van,

The principle is shown in the linked picture (date format: dd/mm/yyyy).

http://jpg.co.il/view/55687b8d9d485.png/

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

The principle is shown in the linked picture (date format: dd/mm/yyyy).

http://jpg.co.il/view/55687b8d9d485.png/

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

I am trying to create a spreadsheet that shows the week the date occurred. For example: The date is 1/02/2014. The range for that date is: Dec 30 - Jan 03, 2014. It is always a Monday - Friday date.

@Barb,

In the attached link you'll find a solution which jumped out of my sleeve.

I'm sure you will face no difficulty to adapt it to your needs.

http://i62.tinypic.com/ezlh7s.png

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

In the attached link you'll find a solution which jumped out of my sleeve.

I'm sure you will face no difficulty to adapt it to your needs.

http://i62.tinypic.com/ezlh7s.png

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

@Micky

... further, if we change the week-ending date to occur on Fridays vs. Saturdays, how would that change the formula?

... further, if we change the week-ending date to occur on Fridays vs. Saturdays, how would that change the formula?

@Micky

Sorry for the delayed response. The Week Ending Date would be the date of the last day of the week during which the date of the data entry occurred. For example:

Data Entry Date: 4/28/15 (Tue)

Week Ending Date: 5/2/15 (Sat)

Data Entry Date: 5/6/15 (Wed)

Week Ending Date: 5/9/15 (Sat)

Thanks

Sorry for the delayed response. The Week Ending Date would be the date of the last day of the week during which the date of the data entry occurred. For example:

Data Entry Date: 4/28/15 (Tue)

Week Ending Date: 5/2/15 (Sat)

Data Entry Date: 5/6/15 (Wed)

Week Ending Date: 5/9/15 (Sat)

Thanks

Hi,

I need Weeks from any date I use every month. From 1 to 7 it should reflect Week 1, 8 to 14 it should reflect Week 2 and so on...

For eg. 4/9/2015 week should be Week 2

This I need from any formula or anything else except formula "If"

Thanks for your help in advance.

Thanks,

Dave

I need Weeks from any date I use every month. From 1 to 7 it should reflect Week 1, 8 to 14 it should reflect Week 2 and so on...

For eg. 4/9/2015 week should be Week 2

This I need from any formula or anything else except formula "If"

Thanks for your help in advance.

Thanks,

Dave

Thanks. Really appreciate the time spent writing up and sharing this tip. I just used it....very nice.

@Daylen,

Forget all about the my previous formula for cell B4.

Instead, use the following:

=B$1+MOD(B$2+7-WEEKDAY(B$1),7)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

Forget all about the my previous formula for cell B4.

Instead, use the following:

=B$1+MOD(B$2+7-WEEKDAY(B$1),7)

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

@Dayleת

The principal goes like that:

* In cell B1 type the first date of the month.

* In cell B2 type the day of week (1-7)

* In cell B4 the formula: =B1+CHOOSE(WEEKDAY(B1),B$2-1,B$2+5,B$2+6,B$2+3,B$2+2,B$2+1,B$2)

* In cell B5 the formula: =IF(MONTH(B4+7)>MONTH(B$1),"",B4+7)

Copied down to cell B9.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

The principal goes like that:

* In cell B1 type the first date of the month.

* In cell B2 type the day of week (1-7)

* In cell B4 the formula: =B1+CHOOSE(WEEKDAY(B1),B$2-1,B$2+5,B$2+6,B$2+3,B$2+2,B$2+1,B$2)

* In cell B5 the formula: =IF(MONTH(B4+7)>MONTH(B$1),"",B4+7)

Copied down to cell B9.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

I am trying to incorporate this cretieria in my dashboard. When i Select day of the week (Eg. Sunday) for a particular month (Nov 2014) all the sundays in the month of Nove should be displayed. (Like : 1 column should show : 02Nov14, another cell should show 09Nov14, another cell should reflect 16Nov14, 23Nov14.

Is this possible ?

Thanks for your help in advance

Is this possible ?

Thanks for your help in advance

@Barb,

What do you, exactly, mean by: "week ending date" ?

Could you provide 3-4 examples in which you calculate and present the requested results ?

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

What do you, exactly, mean by: "week ending date" ?

Could you provide 3-4 examples in which you calculate and present the requested results ?

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2014)

ISRAEL

Thanks for this. I generate a report with entry dates on the specific day a data point was entered into a system. From that specific entry date, I would like to derive the week ending date for the week in which it occurred. Is there any way to accomplish that one?

Thanks!

Thanks!

The report I generate is a live update file and I needed the date to automatically show the current week ending information. I tried the tip for week ending on a Friday but it wound up ending on Sunday. I put the following tweaks to the formula and voila!!! It worked! I was so excited ;) Thank you!

=TODAY()+5-(WEEKDAY(DATE(A1,1,1)+1))

=TODAY()+5-(WEEKDAY(DATE(A1,1,1)+1))

Great tip! Thanks

what if i wanted excel to calculate the week # for a business calendar that counts the week ending Saturday and month ending (regardless of any day)

So in 2012,we'll have 62 weeks.

I want to create a formula that gives me the week number based on todays date.

Thanks

So in 2012,we'll have 62 weeks.

I want to create a formula that gives me the week number based on todays date.

Thanks