E-mail:

# Weeknum Function

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!

## Similar Excel Tutorials

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 partic ...
Output the Worksheet Name in a Cell in Excel - UDF
UDF to return the name of the worksheet in Excel on which this function is located. There are no arguments for thi ...
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function r ...
Output the Name of the Current Excel Workbook Including Extension - UDF
Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function ...

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 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
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
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
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up 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:

=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.

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)

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:

=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.

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

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

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!

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.

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 2-11

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.

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=CurrentWeek-StartWeek 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

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 MS-Excel formula to calculate this with out using pivot table.

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

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?

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

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

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

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