Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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

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

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

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
Return the Max Number from a Range That is Within a Minimum and Maximum Target Value in Excel - UDF
- Return the highest number between two numbers that is in a range of cells with this UDF (user defined function) in Excel

Similar Topics







Hi

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.

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("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?

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=CurrentWeek-StartWeek 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((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,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 need a formula for excel 2007 to calculate the week number if the week starts on a Saturday, I noticed that the Weeknum(A1,16) is not working for 2007

When I use Weeknum(A1,1) I get week 18 for 04/05/2013 and I need week 19

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



Hi

I'm trying to produce a cell that displays the week number one week from now.
I already have the previous week displayed using the formula: ="Week "&(WEEKNUM(NOW()-1))&" Pay"
For some reason, using the formula ="Week "&(WEEKNUM(NOW()+1))&" Pay" is just returning the current week number.

Thanks in advance for your help.

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 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 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'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1
but when I substitute it for WEEKNUM in my formula, it doesn't work:
=IF(INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)>=40,INT(('3930'!I4-DATE(YEAR('3930'!I4),1,1))/7)+1*('3930'!I4)-40,INT(('3930'!I4-DATE(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


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!!


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 !


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