Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

Start/end Dates Of A Week Given Just The Weeknum() Value

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I see weeknum() can return a week number given a date.
However I need to reverse this procedure and calculate the START and END
dates for a week given just the weeknumber [weeknum() ] for the week

for example, given

week number 50 I need to return Dec 5 (start) and Dec 11 (end)
week number 51 I need to return Dec 12 (start) and Dec 18 (end)

thanks , David

View Answers     

Similar Excel Tutorials

Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
Get the Last Day of the Month in Excel
How to get the last day of the month, including the date and day of week, for any date in Excel.  This method allow ...
Get the Day of the Week (1 to 7) for a Date in Excel - WEEKDAY
Use a function in Excel to get the number of the day in a week, from 1 to 7.  This allows you to use your dates to ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...

Helpful Excel Macros

Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
WEEKNUM UDF for Excel Versions That Don't Contain This Function - UDF
- This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the

Similar Topics


Using Excel 2003.
Just notise a that I got a fauly week number when using weeknum(2010-04-29,1) it return 18

I think it has to do with the preaty odd start of this year. In sweden we start counting weeks 1 in january from the week with at least 4 days in, and the wees start on Mondays, in 2010 the week number one are starting with 4th of January, in outlook it is a setting for this, how to handle it in Excel.


Good day all

I am trying to produce a worksheet that will assist me in producing a cash flow based on start and finish dates, of a project, and a value per week.

Basically I have start dates in column a, finish dates in column b and value per week between those dates in column c. Row 1 contains week beginning Monday dates.

I want a formula in the grid that will look up the week beginning date compare it with the start and finish dates and if it falls between those two insert the relevent value in that cell.

I can write the formula and get it to work in one cell but it is a pain copying that into the grid since when you copy across the grid one set of values need to remain constant and then copying down another set, if that makes sense, which seems awfully time consuming, perhaps there is an easy way?

So I tried naming the dates across "dates", the start and finish "start" and "finish", original huh, and the resultant formula comes out as:-

I've got a table of week numbers for 2008 - 1 to 52, and the start and end dates of those weeks. What I'm trying to think of is a way where I can take another table of dates, and find which week number it falls within.

I'd imagine the approach would be to find the week number where the data I provide is less than the end of week, AND greater than the start of week. Alternatively, can Access calculate the weeknum like excel can? I tried this and it didnt recognise the function. I know I need only take 1 away from the number of weeks to find the number I need.

Why does Weeknum say there are ONLY 2 days in the first week of 2011? Aren't there 7 days in a week?

=Weeknum("1-1-2011",2) = Week 1
=Weeknum("1-2-2011",2) = Week 1
=Weeknum("1-3-2011",2) = Week 2

(I'm assuming the first day of the week is a Monday, but I still get wacky results if I start the weeks on a Sunday.)

What's going on?


Looking for a formula that works basically like the WEEKNUM function in reverse in ISO format. So, if given 3/2007 (week 3, year 2007) it will return Jan 15, 2007 (Monday, first day in week 3 of 2007).

Not sure where to start. Any help would be appreciated.

Hi there,

I saw there are some weeknum question already floating around but I couldn't find one similar to my situation.

I have an ongoing quarterly report where I need to take dates from one report and generate which week in the quarter the date falls into. Example and details below.

Quarter start date varies. Current quarter starts on 5/1/2011
Quarter end date varies. Current quarter ends on 7/30/2011 (not sure if this matter. I can always reset the start date)
Week start dates are Sundays
The date format's I'm using are for example 3/14/2011
One other catch is I need to factor in a week 0. That is, any dates that are before the first week of the quarter need to be week 0.
Date range is A2 to A50 or so...

An example of the above situations would be:
3/14/2011 is week 0
5/3/2011 is week 1
5/10/2011 is week 2
and so on...

If you could also explain how I can update the formula to work in the future with different quarter start dates I would extremely appreciate it. Maybe referencing a cell where I put the quarter start date?...

Thanks for any help you can give. I tried giving as much information as possible but if I left anything out let me know.

Thanks again!

Hi folks,

I have a date for the start of a week, which will always be a Monday and I need to work out how many weeks have gone by since that date.

I worked out that the week number of the given date was =WEEKNUM(g3,2) and that the the current week number was =WEEKNUM(TODAY()). However, I had to assign variables to each of these as =WEEKNUM(TODAY())-WEEKNUM(G4,2) gave me an answer of "01/01/1900"!


So using these variables, NumOfWeeks=CurrentWeek-StartWeek gives me the required answer, but how can I transfer this into VBA?



Hello everyone. I am using the WEEKNUM function to gather data from a running list. There are six columns which contain a quantity and then a date in the next column. My formula compare the week number of those dates against today's date. If they are the same week then the quantity is summed with the rest of the matches for that row. I the date isn't within the same week then the quantity for that date isn't added in. The problem I foresee is the last week of the year. I will have data that's in the 52nd week of 2008, and other data that's in the 1st week of 2009. When I get into the first week of January I will lose the data from December. Can anyone help think of a way to keep that data? Below is a sample formula. Thanks!


Hello again.

I am this time trying to get a week number and have it display "Week 0", "Week 1" etc up to "Week 12". I have tried ="Week "&weeknum(a1,2)/4 but I am not sure how to get the "Week 12" to reset back to "Week 0". A1 is =today() so the formula above displays "Week 8" when I really want it to equal "Week 6" but subtracting two will not work as when I start a new year the formula will not work.

So, January 1, April 1, July 1, October 1 all equal "Week 1", with the week previous to this being "Week 0".

I am putting the formula into D1 of the attached sheet.

I know it is long and difficult so thanks for whoever takes the time to look at this.


I am running some tests on machinery and need to work out the number of hours the machinery has run per week. I need to do this on a Monday - Sunday based week. I can use the WEEKNUM function when the data fits neatly into a date range which is within one week and then SUMIF using the relevant weeknumber, but the problem I am having is when the test runs from say a Thursday to a Tuesday and spans two week numbers.

I have columns with 'start date/time' and 'stop date/time' in the format '01/12/2010 15:27'. Then a column which works out the total time run between the two dates.

I want to use the WEEKNUM function, and understand that you can choose for your week to start on a Sunday or Monday. But my week here at the office begins on a Saturday.

How can I get this formula to work for me?

I have a spreadhseet where I enter the date something shipped into a column. One entry per date = 1 shipped.

I want to be able to show actual shipped by week using a week number.

I have an external array that has 3 columns:

Week Start | Week End | Week Number

Week start would always be monday and week end would always be friday. They start at week 1 and go down week 2, week 3 ets.

I'd like to test a ship date against this array and then be able to show a cound of all the week 1 dates, all the week 2, 3 etc.

This should be simple but I'm just not getting it...thus I come to you.

Thank you


I am developing an event management calendar. In that, in cell number 'D2' I have created an option to enter the date of the week (DD/MM/YYYY format).

In column B, in the range of B5 to B32, I have the number of weeks before which the various activities for that event to start. These values are fixed and are numerical.

I am trying to get the start date of the week in column E5 to E32. I am trying to calculate the actual week number from these two values and with that trying to get the start date of the week. But, as the dates are varied, I am getting some junk values.

Please help me in this regard.



I am developing an event management calendar. In that, in cell number 'D2' I have created an option to enter the date of the week (DD/MM/YYYY format).

In column B, in the range of B5 to B32, I have the number of weeks before which the various activities for that event to start. These values are fixed and are numerical.

I am trying to get the start date of the week in column E5 to E32. I am trying to calculate the actual week number from these two values and with that trying to get the start date of the week. But, as the dates are varied, I am getting some junk values.

Please help me in this regard.



I'll try to explain this the best way I can.

I've been asked to do a resource matrix based on complexity of work and duration of a project.

Complexity would be values of 0-5. Duration would be based on a start date and an end date. I think the WEEKNUM function may work out here [e.g. week 9 (start) week 45 (end); the difference would be 36 (weeks)].

I would need to take the start week and populate the count from week 9 all the way through to week a loop through the data and continue to populate each cell for each week that there is activity (or duration/difference between the two dates).

So if there is a project that starts week 1 and ends week 5 and another project that starts week 2 and ends week 4...the table should look like this...

For complexity 0 and PM Joe; week1=1, week2=2 week3=2, week4=2, week5=1.

Any help would be greatly appreciated, I'm going bald

We have a fiscal calendar which starts Oct 1. I would like to display the proper week numbers. I worked out a formula which seems to work (except for week 53) but it would be better if I didn't have to rely on other users having the Analysis Toolpak installed. My date is located in '3930!I4' and this is the formula that works with the toolpak:

I searched and found an alternate formula to replace WEEKNUM:
but when I substitute it for WEEKNUM in my formula, it doesn't work:
Returns the number 39723 rather than (week) 1
Can anybody help adjust my formula? Thanks

When I use the WeekNum function =WeekNum(A2,1) to pull the week number for a row of cells with consecutive dates excel is saying that Tueday is the first day of each week. What the heck is gonig on and how do I fix this? Excel seems to be off by two days - the clock in my windows task bar seems displays the right day. I also tried this on another computer and it did the very same thing. The two computers I tried it on are totally separate in everyway. Please Help!!


i am trying to figure out why the if statement which has the weeknum function in the attached excel sheet does not work when the start date is 27/11/2011 and finish date is 26/03/2012.

The if statement looks at the start date for a week number and the end date for a week number and puts an "x" in the cells if the criteria is met.

Please help !

Hey Anybody,

I'm new here at but I have been a regular at another forum which for some reason has closed for new threads.

I have a formula question if anyone can help me. At the moment I have Excel 2007 and using the WeekNum formula with the week starting on Monday (2).

Thats great, but I need the week to start on Tuesday not Monday which the WeekNum formula can't do, so is there anyway I could do this?

Any help would be great.



in my table are lost of dates within several years.
in order to know the week number of the dates, I use


however, this is sometimes calculating week number 53.
what would be a more correct way of doing this?

I want to create a function which will provide me week number for a date. The formula weeknum() doesn't solve my problem. The excel week number is defined from january while I want week number to start from a different month. The fiscal year of the company starts from 1st week of feb.

The company's year starts from 4th Feb 2008.
if I use the formula weeknum() for the above date, i get the value as 6. But as per my requirements this should be week number 1. I would prefer a function instead of running a macro. I want to call this function from the excel file. see sample data below

Date Excel Week Number My week number My Quarter 4-Feb 6 1 1 15-Feb 7 2 1 3-Mar 10 5 1 12-Mar 11 6 1 18-Mar 12 7 1 15-Apr 16 11 1 9-May 19 14 2

In addition to this, I also want to find the quarter number. Each fiscal year has 4 quarter and one quarter is equal to 13 weeks. So quarter 1 for my data is = (4th feb + 13 weeks). Once first 13 weeks are completed, quarter 2 starts and this goes on till the last quarter (Q4) is reached.
I am weak in writing functions hence need help.

I've created a rudamentary function to calculate the start date of the week for the week number that is passed. Problem is, since this year (2009) started on a Thursday I need to subtract four days. Obviously this won't work come next year since it starts on a Friday, I was going to write this elaborate code to calculate the offset of Sunday from 1/1/yyyy but figured I should ask if anyone else had any better ideas.

do you?


Function WeekNumToDate(weekNum As Integer)
    WeekNumToDate = DateAdd("d", 7 * weekNum - 4, "1/1/2009")
End Function


I was wondering if someone could help.

I know how to generate the current weeknumber and also the current year.

The trouble I have is the year jump when pulling this down as a dynamic formula.

Basically I need to genereate a list of week numbers with the year. This will update every week as the week changes, How to I write a formula to know that when the weeks number has reached 0 to go back to 52 and minus the year by one.

My current sheet has two columns.

Column A This displays the week number which is set with the following formulas

cell a1
cell a2 onwards
This is working out that if the week number reaches 0 to start again at 52

column B is displaying the year, however I want to be able to move the year to 2010 when the weeks jump over.

Is there an easy way to do this?




when i use the weeknum function i'm limited to having the week start either on sunday or monday - but i have maintenance scheduled from thursday through wednesday - is there a way to start the week on thursday?

thanks for your help!

Does anyone know of a way to use the WEEKNUM function but to have it display the week number relative to a specific date? For example, If my column of dates starts at May 7th, where I would like May 7th to appear as Week "1" and have it continue from that point on where May 14 (for example) would be week 2, etc.