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


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Start/end Dates Of A Week Given Just The Weeknum() Value  Excel

View Answers


Hi
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
concerned
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
Similar Excel Video Tutorials
Similar Topics
Hi
Using Excel 2003.
Just notise a that I got a fauly week number when using weeknum(20100429,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.
Zalvi
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:
=IF(AND(WEEKNUM(Dates,2)>=WEEKNUM(Start,2),WEEKNUM(Dates,2)
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("112011",2) = Week 1
=Weeknum("122011",2) = Week 1
=Weeknum("132011",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?
Lawrence
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"!
CurrentWeek=WeekNum(Today))
StartWeek=WeekNum(g3,2)
So using these variables, NumOfWeeks=CurrentWeekStartWeek gives me the required answer, but how can I transfer this into VBA?
Thanks!
Phil
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!
=IF((WEEKNUM(L3,1)=WEEKNUM(TODAY(),1))=TRUE,K3,0)+IF((WEEKNUM(N3,1)=WEEKNUM(TODAY(),1))=TRUE,M3,0)+IF((WEEKNUM(P3,1)=WEE KNUM(TODAY(),1))=TRUE,O3,0)
Hi,
I'm looking for a formula to work out the week number from a list of dates based on the working week starting on a saturday and ending on a friday. Week 1 would need to start 1st Jan regardless of the day of the week. I can't work out how to get weeknum to do the job... any ideas?
thanks
Charlie
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.
http://www.mediafire.com/?sharekey=obfy2g3xef8d4
Hi,
I can calculate the week number based on the date in cell A1 (that represents the first 5 cells in a row(5 work days))and I can use either of these two formula's...
=INT((A1SUM(MOD(DATE(YEAR(A1MOD(A12,7)+3),1,2),
{1E+99,7})*{1,1})+5)/7)
=WEEKNUM(A1,2)
I get a return of let's say "Week 26"
However, Im using a row (that is 10 cells (10 work days)) of dates and if there is a split week then I would like a return of say "week 25/26"
The dates are never constant and are always progressing
Is there such a way to achive this?
Any help would be apriciated
Thanks
Cutter
Hi,
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 have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?
Regards
Hi
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.
Sri
Hello,
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.
Sri
Hello:
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 05. 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 45..run 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
Hello All,
MrExcel.com has never let me down, so when I realize I am spending more time spinning my wheels, I decide to ask!
I have the following table with the expected output.
Project Start Date: 1/15/2003
Week Number
3/2003
4/2003
5/2003
...
...
...
52/2003
1/2004
What I would like to do is have a column of weeknumbers be output from the "Project Start Date" for the first week number (3/2003), then the next weeknumbers be sequential, 4/2003, 5/2003, etc. I would also like the weeknumber column to include the year for the w/yyyy format. Next, this w/yyyy value may roll into 2004, so I would like to be able to have the formula output 1/2004 after 52/2003.
I have the basics of the weeknum function down for the first weeknum 3/2003, =WEEKNUM(C2)&"/"&YEAR(C2) just do not know how to get it to add the next weeks, 4/2003, 5/2003, and do not know how to roll it over to 1/2004.
Thanks for any help!
Brian
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:
=IF(WEEKNUM('3930'!I4)>=40,WEEKNUM('3930'!I4)39,WEEKNUM('3930'!I4)+13)
I searched and found an alternate formula to replace WEEKNUM:
=INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1
but when I substitute it for WEEKNUM in my formula, it doesn't work:
=IF(INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)>=40,INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)40,INT(('3930'!I4DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)+14)
Returns the number 39723 rather than (week) 1
Can anybody help adjust my formula? Thanks
Hi
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 !
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!!
Hey Anybody,
I'm new here at ExcelTip.com but I have been a regular at another forum Ozgrid.com 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.
Cheers
Ben
in my table are lost of dates within several years.
in order to know the week number of the dates, I use
weeknum(A1)
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.
Ex.
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
4Feb
6
1
1
15Feb
7
2
1
3Mar
10
5
1
12Mar
11
6
1
18Mar
12
7
1
15Apr
16
11
1
9May
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.

