Weeknum Function 


Weeknum Function  Excel 
View Answers 
I've been creating a spreadsheet that takes figures from specific dates and summarises into weeks and came accross this problem..
E.g.
=WEEKNUM("20/9/2005")
returns '39'.. it is however week 38!
However, using dates from last year e.g.
=weeknum("14/10/2004")
it returns the correct week 42.
Is it me or is this a bug?... i've tried looking for some sort of patch but no joy!
E.g.
=WEEKNUM("20/9/2005")
returns '39'.. it is however week 38!
However, using dates from last year e.g.
=weeknum("14/10/2004")
it returns the correct week 42.
Is it me or is this a bug?... i've tried looking for some sort of patch but no joy!
Similar Excel Tutorials
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
MODE()  Find Most Repeated Value in a List in Excel
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...
Capitalize First Letter of Every Word in a Cell  PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell. This allows you to prepare ...
In Excel you can use a function to capitalize the first letter of every word in a cell. This allows you to prepare ...
Get the Row or Column Number of a Cell in Excel
How to get the row or column number of the current cell or any other cell in Excel. This tutorial covers important ...
How to get the row or column number of the current cell or any other cell in Excel. This tutorial covers important ...
Helpful Excel Macros
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
 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 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
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Excel Macro to Save a Specific Worksheet as a New File
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
 This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Macro to add a New Line to Message Box Popup Windows in Excel
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
Similar Topics
Formula below returns Year Week format.
e.g. 11/06/08 entered in cell C13 returns 0845
Code:
however entering 12/29/08 returns 0853, but management wants it as 0901.
01/05/09 returns 0902. Is this how Weeknum calculates, do I need to write my formula to if 53 then... or am I missing something with Weeknum?
Thanks,
Chas
e.g. 11/06/08 entered in cell C13 returns 0845
Code:
=VALUE(RIGHT(YEAR(C13),2)&TEXT(WEEKNUM(C13),"00"))
however entering 12/29/08 returns 0853, but management wants it as 0901.
01/05/09 returns 0902. Is this how Weeknum calculates, do I need to write my formula to if 53 then... or am I missing something with Weeknum?
Thanks,
Chas
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.
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.
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)
=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)
I can't seem to figure out how to get weeknum to work within sumproduct.
In column A I have dates and based on a value in B I would like to sum C.
Any thoughts on what I am missing here?
Code:
In column A I have dates and based on a value in B I would like to sum C.
Any thoughts on what I am missing here?
Code:
=IF(WEEKNUM($A1,2)=WEEKNUM($A2,2),"",SUMPRODUCT((WEEKNUM($A$2:$A$19,2)=WEEKNUM(A2,2)),($B$2:$B$19=B$2),$C$2:$C$19))
I need to take a column of dates starting from Jan 1 of the current year to Dec 31 of the current year (cells from A2 to A366) and assign each week of that list by the function WEEKNUM and assign it to a dynamic range name.
I just don't have the Excel experience to know the best way or what the most efficient options are.
I know I can use the function OFFSET to help, but I'm not sure how to work the "reference" argument dynamically. For a static example Week1=OFFSET(A2,7,1) Week2=OFFEST(A9,6,1) Week3=OFFEST(A16,6,1)etc. works statically for the year 2006, but for the year 2007 Week1=OFFSET(A2,6,1) Week2=OFFSET(A8,7,1) Week3=OFFSET(A15,7,1) etc.
From year to year I need the WEEKNUM to dynamically reference the correct range of cells associated with it's WEEKNUM.
Ultimately I am using these dynamic range names to sum values in cells that contain daily sales figures in each department so I can reference week to week each year.
Thanks.
I just don't have the Excel experience to know the best way or what the most efficient options are.
I know I can use the function OFFSET to help, but I'm not sure how to work the "reference" argument dynamically. For a static example Week1=OFFSET(A2,7,1) Week2=OFFEST(A9,6,1) Week3=OFFEST(A16,6,1)etc. works statically for the year 2006, but for the year 2007 Week1=OFFSET(A2,6,1) Week2=OFFSET(A8,7,1) Week3=OFFSET(A15,7,1) etc.
From year to year I need the WEEKNUM to dynamically reference the correct range of cells associated with it's WEEKNUM.
Ultimately I am using these dynamic range names to sum values in cells that contain daily sales figures in each department so I can reference week to week each year.
Thanks.
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
=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
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
=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
my company uses fiscal week (like most others do), but when referenced it's always four digit....i.e.FW02, FW09, FW45 etc.
in using the 'weeknum' formula/function, in fiscal week of one number....the display shows FW3, FW6 etc.
how can i get excel to display FW03 and FW08 and so forth?
i can use both of the following formulas....
"FW0"&WEEKNUM(J1,1)1
"FW"&WEEKNUM(N1,1)1
where "FW" and "FW0" will get what i want but how would i write the formula to display what i want no matter which date i actuall enter into the adjacent cell?
thanks guys!
in using the 'weeknum' formula/function, in fiscal week of one number....the display shows FW3, FW6 etc.
how can i get excel to display FW03 and FW08 and so forth?
i can use both of the following formulas....
"FW0"&WEEKNUM(J1,1)1
"FW"&WEEKNUM(N1,1)1
where "FW" and "FW0" will get what i want but how would i write the formula to display what i want no matter which date i actuall enter into the adjacent cell?
thanks guys!
Was given following formula which worked fine until the year changed 
=WEEKNUM(A2)WEEKNUM(DATE(2005,3,1))+1
My spreadsheet runs timestamps from 1 mar 2005 thru to 1 mar 2007. 1 Mar 2005 was week 1 and worked great thru to 31 dec 2005 which was week 44.
As soon as the date changed to 1 Jan 2006  formula returned  8 !!!!
I have tried fiddling around but still have no success in January 2006 carrying on from 2005 increasing from 44 onwards.
Spreadsheet shows
Date (A2 etc) ........ Week number
01/03/2005 ...................1
thru to
31/12/2005 ...................44
then !
01/01/2006 .................. 8
Would appreciate any help again  thanks in advance
Chaz
=WEEKNUM(A2)WEEKNUM(DATE(2005,3,1))+1
My spreadsheet runs timestamps from 1 mar 2005 thru to 1 mar 2007. 1 Mar 2005 was week 1 and worked great thru to 31 dec 2005 which was week 44.
As soon as the date changed to 1 Jan 2006  formula returned  8 !!!!
I have tried fiddling around but still have no success in January 2006 carrying on from 2005 increasing from 44 onwards.
Spreadsheet shows
Date (A2 etc) ........ Week number
01/03/2005 ...................1
thru to
31/12/2005 ...................44
then !
01/01/2006 .................. 8
Would appreciate any help again  thanks in advance
Chaz
hi, I have lists of dates spanning several years.
How can I modify the weeknum funtion to show "week AND year"
i.e 7th January 2011 would show as 211
How can I modify the weeknum funtion to show "week AND year"
i.e 7th January 2011 would show as 211
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'm looking for a function that will show this: "2014  FW 27"
In cell O4, I have the date, 07/01/14 or July 1, 2014
My log consists of some dates from 2013 & therefore, I need to distinguish the weeks from 2013 & 2014 with the format above
Right now, I have the following function:
=IF(WEEKNUM(O4,1)=53,1,WEEKNUM(O4,1))
In case FW 53 shows up, it will now show as FW 1
I'm looking to put the year in front it & not certain how.
Please advise.
Thanks
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
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
We have an Excel spreadsheet that runs off of a SQL database.
It appears that it is using a "weeknum" function to generate the information in a weekly format by subtotaling the information for each week.
Is there an equivalent function in Excel to tell me what week a certain date per year is?
Chris Nelson
It appears that it is using a "weeknum" function to generate the information in a weekly format by subtotaling the information for each week.
Is there an equivalent function in Excel to tell me what week a certain date per year is?
Chris Nelson
Hi All,
The data I am using for my work is shown below
Team Weeknum
T1 1
T2 1
T3 1
T1 2
T1 2
T1 2
T4 2
T1 2
T2 2
T3 2
T4 3
T5 3
T6 3
T1 3
T1 3
T2 3
T3 3
T4 4
T5 4
T1 4
T2 4
T3 4
T4 4

Question : Total count of T1 in weeknum 1,2,3...
Total count of T2 in weeknum 1,2,3...

I want to fill the following table
Teams weeknum 1 weeknum 2 weeknum 3 weeknum 4 weeknum 5
T1
T2
T3

Give me a MSExcel formula to calculate this with out using pivot table.
Thanks in advance.
The data I am using for my work is shown below
Team Weeknum
T1 1
T2 1
T3 1
T1 2
T1 2
T1 2
T4 2
T1 2
T2 2
T3 2
T4 3
T5 3
T6 3
T1 3
T1 3
T2 3
T3 3
T4 4
T5 4
T1 4
T2 4
T3 4
T4 4

Question : Total count of T1 in weeknum 1,2,3...
Total count of T2 in weeknum 1,2,3...

I want to fill the following table
Teams weeknum 1 weeknum 2 weeknum 3 weeknum 4 weeknum 5
T1
T2
T3

Give me a MSExcel formula to calculate this with out using pivot table.
Thanks in advance.
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 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 have a worksheet with almsot 40 items and everyday of the year, but i need
something to tell me quickly if there is something coming up this week. I can
do this for each day and column, but i am having trouble getting a formula i
can use to look at all the items, and count up all the items due that week.
This is what i have so far, the inital criteria "1" tells me there is
something due, the other is to see if it is the current week;
=COUNTIF(D16,1 & (IF(WEEKNUM(E16,2)=WEEKNUM(TODAY(),2),,1)))
something to tell me quickly if there is something coming up this week. I can
do this for each day and column, but i am having trouble getting a formula i
can use to look at all the items, and count up all the items due that week.
This is what i have so far, the inital criteria "1" tells me there is
something due, the other is to see if it is the current week;
=COUNTIF(D16,1 & (IF(WEEKNUM(E16,2)=WEEKNUM(TODAY(),2),,1)))
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?
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?
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
I need to display the week numbers of given dates, but want the weeks to run saturday through to friday. Weeknum only seems to work with weeks starting on sunday or monday rather than saturday. I am an excel beginner so please help!!
thanks
I need to display the week numbers of given dates, but want the weeks to run saturday through to friday. Weeknum only seems to work with weeks starting on sunday or monday rather than saturday. I am an excel beginner so please help!!
thanks
I am using WEEKNUM in Excel to do some COUNTIF functions and have problems
with matches because Excel returns a single digit rather than 2 of weeks 1
throuhg 9 of any given year. Using teh COUNTIF function 20051 then
mathmatically is the same as 200510. Is there some way to format the week
returned to be 2 digits?

RonB
with matches because Excel returns a single digit rather than 2 of weeks 1
throuhg 9 of any given year. Using teh COUNTIF function 20051 then
mathmatically is the same as 200510. Is there some way to format the week
returned to be 2 digits?

RonB
Hi
Does anyone know why the weeknum function always returns 53 weeks instead of 52
ie 31/12/2006 = 53
31/12/2007 = 53
31/12/2008 = 53
Rgs
Thomas
Does anyone know why the weeknum function always returns 53 weeks instead of 52
ie 31/12/2006 = 53
31/12/2007 = 53
31/12/2008 = 53
Rgs
Thomas
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
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
Hi I have a formula
=IF(I1239="","",IF(OR(H1239="Induction",H1239="One to One"),WEEKNUM(I1239)WEEKNUM(K1239)+1,""))
Which works fine an counts the weeks from the input date I1239 from the initial start date K1239, however if the input date runs into next year 2014 the result ends up as a minus figure eg 47 instead of the next sequential figure which would be say 5 or 6
Hope you can help
many thanks
JSD
Hi everyone,
Hopefully someone can help me with this, it would be greatly appreciated as I have been racking my brain for two days with this formula. Before posting I checked these three posts:
http://www.mrexcel.com/forum/showthr...hlight=weeknum
http://www.mrexcel.com/forum/showthread.php?t=200225
http://www.mrexcel.com/forum/showthr...ight=code+week
My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.
Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 20080703) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.
This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.
Here is a copy of my formulas:
=IF(AND(F8)=0,""*(IF(AND(F8)
Hopefully someone can help me with this, it would be greatly appreciated as I have been racking my brain for two days with this formula. Before posting I checked these three posts:
http://www.mrexcel.com/forum/showthr...hlight=weeknum
http://www.mrexcel.com/forum/showthread.php?t=200225
http://www.mrexcel.com/forum/showthr...ight=code+week
My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.
Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 20080703) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.
This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.
Here is a copy of my formulas:
=IF(AND(F8)=0,""*(IF(AND(F8)