Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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



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 ...
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...
Sorting Data by Date, Text, or Number in Excel
Sorting Data can be done with a few quick clicks of the mouse. I have used the same data as the previous 2 tutorial ...

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







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


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!


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?


Hello

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
WEEKNUM(NOW())-1
cell a2 onwards
IF(A1-1=0,52,A1-1)
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?

Cheers

John


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.


Hi

How can i change week number to start from 1 if my year starts 1/07/2009 if i use function = weeknum() it returns the value 27.


Thanks




I have a list of 200 records.

I want to count the number of records per week number.

I have tried

=SUMPRODUCT(--(WEEKNUM('Completed Rejects'!M$1:M$1000)=Analysis!L2))

Where WEEKNUM('Completed Rejects'!M$1:M$1000) is the list of dates

and Analysis!L2 is the week number (in a number format)

and I have tried

=COUNTIF(WEEKNUM('Completed Rejects'!M:M),Analysis!L2)



Sumproduct gives a #Value
Countif gives a #NUM!


Any help appreciated.


Analysis L M N 1 Week Count 2 22 #VALUE! #NUM! 3 23 4 24 5 25 6 26 7 27 8 28 Excel 2000

Worksheet Formulas Cell Formula M2 =SUMPRODUCT( ( WEEKNUM( 'Completed Rejects'!M$1:M$1000 )=Analysis!L2 ) ) N2 =COUNTIF( WEEKNUM( 'Completed Rejects'!M:M ),Analysis!L2 )



Completed Rejects L M N 1 Responsible Completion Date 2 Complete 12/07/2010 29 3 Complete 30/06/2010 27 4 Complete 16/07/2010 29 5 Complete 15/07/2010 29 Excel 2000

Worksheet Formulas Cell Formula N2 =WEEKNUM( M2 ) N3 =WEEKNUM( M3 ) N4 =WEEKNUM( M4 ) N5 =WEEKNUM( M5 )



^ these weeknum calculations aren't actually used and have to be deleted (I can't use a helper column in this case)


hi guys

Can anyone please help me to find the week start date(monday) and weekend(sunday) using the week number. I am able to find the weeknumber using the code below but don't know how to find the start date and end date of the week.
Code:

Dim iNumberOfTheWeek As Integer

iNumberOfTheWeek = DatePart("ww", Now())


Regards
Aman


I have an excel sheet that has a field called "Decision Date". It is a numerical date (ex: 1/5/09 indicating January 5th 2009).

I need to turn that date into the week that it falls into within a particular quarter of a year. For example:

1/5/09 would be Week 2 (it is in the second week of January, and 2nd week of the quarter)
2/3/09 would be week 6 (6th week of the quarter).

And then I need the formula to start over once each quarter restarts... for example, April 1st would be week 1 (1st week of the second quarter).

Can Excel do this? Convert a date to the week it falls within?


Hi All,

I am trying to calculate the week start date (first day of the week is Monday) in a set of dates.

For example,

for 7/1/2011 (Friday) the week start date will be 6/27/2011 (Monday).
for 7/6/2011 (Wednesday) the week start date will be 7/4/2011 (Monday)
for 7/26/2011 (Tuesday) the week start date will be 7/25/2011 (Monday).

How do you implement this with a formula?

Thanks!


i have a table with dates running down one column and i find it hard to keep track of which week or row i am in, as there is a lot of data to look at. i would like to be able to put the start date of a given week into a cell, and for that row to throw up a thick black border. the dates in the column relate to the start date of that week.

eg:


date = 21/01/08


07/01/08
14/01/08
===============================
21/01/08
===============================
28/01/08
04/02/08


and then of course to undo the border when the date is changed.


i'd appreciate any help, cheers


My company tracks many milestones and project dates by work week. To complicate matters our fiscal year starts in July. Below are sample work weeks that correspond to the given dates.


DATE WW
1/1/2007 ww27'07
6/28/2007 ww52'07
7/3/2007 ww01'08
12/25/2007 ww26'08
1/1/2008 ww27'08
6/25/2008 ww52'08

The WW column was generated by a long and inelegant formula that I wrote. Wanted to see if there was a much simpler way to achieve this. My formula:

="ww"&TEXT((IF((WEEKNUM(A2,2))<27,(WEEKNUM(A2,2))+26,(WEEKNUM(A2,2))-26)),"00")&"'"&TEXT(MID((IF((WEEKNUM(A2,2))<27,(YEAR(A2)),(YEAR(A2))+1)),3,2),"00")


Thanks for the help!
Pat



p.s. This is a great forum.


Hi,
I would appreciate some help with a problem i have on my spreadsheet.

I have a column with week numbers (N58:N109) and a column with the number of items used per week (O58:O109). The weeks are from week 1 to week 52. the useage column automatically updates daily (it is linked).
I also have todays weeknumber displayed in cell F1 (based on today's date).

Is it possible to get the average of the last 8 weeks, based on todays week number?

so for example this is week 37, i would want to see the average number of items per week from week 30 to the current week.

and next week it would display from week 31-38 and so forth.


I hope that is clear, thanks in advance for your help!

Shifei


Is there a way to redefine a work week to be 4-day (Mon-Thurs.).

I need to calculate start and end dates based on a net amount of work days.


For instance, if the start date is 8/9/2010 and the 'job' will take 14 working days (based on a 4-day week) what will be the end date?

Thanks,
Eitan


Hello,

I'm trying to find a way to have a range of dates from Monday through Sunday display as the week ending date on Sunday.

For example -- today is 11/29/2010, I can use WEEKNUM(A1,2) and get week 49 but I'd like that to show as 12/5/2010.

Thanks for your help!

Mike


We have a four week work rota cycle. So week 1, people do x, week 2 the same do y, week 3, z, 4 something else, then it all goes back to 1 and starts again.

I can get excel to work out the week number in the calendar year (ie week 1-52) with Quote:

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7)

but don't know how to convert that into week 1,2,3 or 4 of our cycle.

By the way, Week 1 of the year, starting 03 Jan was week 3 of our cycle.

Any ideas?


I've got a sheet where users can input a start date into cells B3:B17 and an end date into cells C3:C17. All start dates are Mondays; all end dates are Sundays; none of the periods overlap.

I need a continuous list of start dates for each week in each of the periods in cells B20:B? (dependent on number of weeks in each period). Also need corresponding week end dates in cells C20:C?

So if B3 = 03/03/2008, C3 = 16/03/2008 and
B4 = 24/03/2008, C4 = 30/03/2008 then

B20 = 03/03/2008, C20 = 09/03/2008
B21 = 10/03/2008, C21 = 16/03/2008
B22 = 24/03/2008, C22 = 30/03/2008

Hope this is clear - sorry not allowed any downloads at work so can't display graphically.




My fiscal year starts on July 29th and I have a total of 52 weeks in a year. Sunday is considered the first day of the week. How do I calculate the week number of a specific date? For instance, if the date is 7/15/2013, the week number should be 51.

My fiscal year starts on October 1st and I have a total of 53 weeks in a year. Saturday is considered the first day of the week. How do I calculate the week number of a specific date? For instance, if the date is 4/10/2010, the week number should be 29.


Is there a formula i can use to return the work week for a date - starting at week 1 for the first week of january and so on?


Hi All,

I need some help with the best way to do this. I've attached the spreadsheet.

I want to display by data in the format in columns G through O. It is currently in the format of columns A through E.

Is there a formula that if A4 is week 26 of 2006 and a monday set cell I4 equal to D4. If A5 is week 26 of 2006 and a tuesday set cell J4 equal to D5. If A6 is week 26 of 2006 and a wednesday set cell K4 equal to D6, etc. Eventually I will have a few years worth of data in the A through E columns.

I tried using the sumproduct, but I keep getting the #value error. I tried inserting two columns after column A and calculating the weeknum and weekday values of A4, but I still can't get the right value in the G through O columns.

Any suggestions is appreciated


Hello,
I am attempting to create a pivot table to show counts by Week Number for three different date columns. I am struggling with the Count not working correctly (the second and third date field counts are being grouped/pivoted under the first date field).

Each individual Part (row) has a 'Received Date', 'Analyzed Date', and 'Ship Date'. I have converted the dates to 'Week Number'. I now want to display a Pivot Table that shows the Count of units by Week Number for each of these 3 date fields. Please see generic example below:

Sample Excel Sheet Layout:
Serial Number Week # - Date Received Week # - Date Analyzed Week # - Date Shipped 100000 1 2 2 200000 3 3 3 300000 2 2 3

Sample of what I would like the Pivot Table to look like:
Count of Date Received Count of Date Analyzed Count of Date Shipped Week 1 1 0 0 Week 2 1 2 1 Week 3 1 1 2

Appreciate any help you can provide.


I am trying to calculate the week number for a date in a month, i have a formula to calculate it from the start of a month but i need the count (ie week 1) to begin on the 18/01/2010 to the 14/03/2010 if this makes sense? Can anyone help?




Hi all,

I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. Please see the attached excel file for reference.

What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.

Thanks in advance for anyone that can help.

Hi guys,

How can I create a formula that will calculate an end date for a period of 26 weeks when I already have a start date? The 26th week will always end on a friday. however, week one can be defined as any working day.

Thanks in advance!