>

Weeknum Function (Alternate Topic Results)


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 Topics
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
CREATE TABLE [dbo].[ScheduledTask] (
[WeekNum] [int] NULL ,
[DayNum] [int] NULL ,
[DayName] [char] (30) NULL ,
[PrevCompletedTime] [char] (8) NULL ,
[StartedTime] [char] (8) NULL ,
[PrevCompleted] [char] (30) NULL ,
[Started] [char] (30) NULL ,
[Busy] [char] (30) NULL ,
[Idle] [char] (30) NULL
) ON [PRIMARY]
GO
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','04:58:40','07:00:15','20041220 04:58:40','20041220 07:00:15','00:00:00','122')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','03:19:56','07:01:10','20041221 03:19:56','20041221 07:01:10','00:00:00','222')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','07:01:10','23:58:55','20041221 07:01:10','20041221 23:58:55','00:00:00','1017')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','02:02:27','06:41:57','20041222 02:02:27','20041222 06:41:57','00:00:00','279')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','06:41:57','06:42:40','20041222 06:41:57','20041223 06:42:40','00:00:00','1441')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','02:51:18','06:45:09','20041224 02:51:18','20041224 06:45:09','00:00:00','234')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','06:45:09','00:01:27','20041224 06:45:09','20041225 00:01:27','00:00:00','1036')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','21:19:52','23:07:19','20041225 21:19:52','20041225 23:07:19','00:00:00','108')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','21:19:43','23:07:20','20041226 21:19:43','20041226 23:07:20','00:00:00','108')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','08:14:50','09:52:05','20041227 08:14:50','20041227 09:52:05','00:00:00','98')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,1,'Monday','21:19:43','22:32:26','20041227 21:19:43','20041227 22:32:26','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','08:12:05','09:25:17','20041228 08:12:05','20041228 09:25:17','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,2,'Tuesday','21:30:05','23:07:20','20041228 21:30:05','20041228 23:07:20','00:00:00','97')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','07:26:00','08:53:22','20041229 07:26:00','20041229 08:53:22','00:00:00','87')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','16:59:43','17:36:07','20041229 16:59:43','20041229 17:36:07','00:23:51','37')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','16:59:43','17:36:07','20041229 16:59:43','20041229 17:36:07','00:41:43','37')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,3,'Wednesday','21:29:01','23:07:20','20041229 21:29:01','20041229 23:07:20','00:00:00','98')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','11:49:32','13:04:25','20041230 11:49:32','20041230 13:04:25','00:00:00','75')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','15:04:07','17:10:50','20041230 15:04:07','20041230 17:10:50','11:18:07','126')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,4,'Thursday','21:20:46','22:33:01','20041230 21:20:46','20041230 22:33:01','00:00:00','73')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','07:54:00','11:07:03','20041231 07:54:00','20041231 11:07:03','00:00:00','193')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','15:21:49','18:05:32','20041231 15:21:49','20041231 18:05:32','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,5,'Friday','21:19:43','23:05:35','20041231 21:19:43','20041231 23:05:35','00:00:00','106')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','07:30:00','10:38:32','20050101 07:30:00','20050101 10:38:32','00:00:00','188')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','15:21:49','18:05:37','20050101 15:21:49','20050101 18:05:37','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,6,'Saturday','21:19:43','22:31:54','20050101 21:19:43','20050101 22:31:54','00:00:00','72')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','10:42:21','11:54:23','20050102 10:42:21','20050102 11:54:23','00:00:00','72')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','15:21:43','18:05:41','20050102 15:21:43','20050102 18:05:41','00:00:00','164')
INSERT INTO [ScheduledTask]
([WeekNum],[DayNum],[DayName],[PrevCompletedTime],[StartedTime],[PrevCompleted],[Started],[Busy],[Idle])VALUES(NULL,7,'Sunday','21:29:03','23:07:19','20050102 21:29:03','20050102 23:07:19','00:00:00','98')
Given the DDL above and import it into a csv file, I need to generate a
chart using EXCEL that display the idle window based on the following
specs....
Let's say I have a fiscal year that starts on 12/20 and ends on 12/19 the
next year.
My ultimate goal is trying to display the Idle window of a series of
scheduled tasks on each day of the week, on a Excel Spreadsheet. It may have
the look of the MS Project Schedule Task.
My Challenges a
1. My xaxis may be the DayName (7 values) with only and with minor scale
that contains hour. My yaxis may be grouped by the actual date (14 days),
with the values of the actual prevCompleted and Started (29 series in this
sample). If this is too complicated to accomplish, then my Yaxis may be
group by the 29 series instead of the 14 days. If I must write a query to
iterate my data after 7 days so that the Excel Chart can understand it, how
the query should be written? If that is not neccessary, how?
2. How can I generate weeknum based on the given data?
My ultimate goal of this chart is to identify the idle windows every week.
Many 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 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
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 there.
I need to use weeknum() for a number of situations in a project I'm working on.
But when I use =WEEKNUM(DATE(2010;1;31)) it returns 6, instead of 4 as it should. Using it on today's date returns correct number.
I'm clueless as to why...
Any ideas?
Hi,
I'm having a column A with a lot of dates of the form yearmonthday and column B contains the corresponding numbers 1 or 2. Now I want to check for example, how many twos did I have on week 30? I was thinking something like:
=SUMPRODUCT((WEEKNUM(A2:A1000;2)=30)*(B2:B1000=2))
I know this is wrong because weeknum wants only one date at a time.. or is it a way around this? I prefer not to create another column with corresponding week numbers if I can avoid this.
Thanks in advance
Andreas
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)
Hurray and greetings!
This is my first post in this forum.
Maybe I am wrong, but I tried the weeknum() as described in http://www.exceltip.com/st/Calculati...Number/89.html
This function does not give the right weeknumber for all dates fed to it. For instance the date 04.12.2005 (dd.mm.yyyy) would be assigned week 49 which is wrong. 48 is the right answer. I'd rather use this formula (B2 is the cell where the date is) INT((B2DATE(YEAR(B2WEEKDAY(B21)+4),1,3)+WEEKDAY(DATE(YEAR(B2WEEKDAY(B21)+4),1,3))+5)/7)
Anyone have any comment to this?
Hi the
I want to say that this forum has been SO helpful to me and I appreciate the quick responses!
I have a list of dates going back to 2005 and I want to assign week numbers to them. For example,
1/7/2005 would be week 1. (=weeknum,a1).
12/31/2005 would be week 53
1/5/2006 would be week 54 (not week 1)
12/31/2006 would be week 106 (or 105, not sure)
My question is how to assign week numbers after the 53rd week of a year if I have a long list that begins in 2005 all the way to 2011 and beyond.
Thanks much
Hi All,
I got great help with my last weeknum problem and was ploughing along until this happened.
One of the columns with dates has sporadically missing dates (messing up my offset element). I wanted to sum advances where the dates were equal to a set week number. My problem is I keep getting #num! errors no matter what the combination.
The original formula was
=SUM(IF(OFFSET(Data!$F$2,0,0,COUNTA(Data!$F$2:$F$10000),1)=PINDESC!B16,IF(INT((OFFSET(Data!$I$2,0,0,COUNTA(Data!$I$2:$I$ 10000),1)DATE(YEAR(OFFSET(Data!$I$2,0,0,COUNTA(Data!$I$2:$I$10000),1)),1,1)WEEKDAY(OFFSET(Data!$I$2,0,0,COUNTA(Data!$I$2:$I$10000),1)1))/7)+2='YTD Summary  PL week on week'!$C$1,OFFSET(Data!$H$2,0,0,COUNTA(Data!$G$2:$G$10000),1))))
What I'm trying to do is;
If pindesc = number, if EQDAT = a weeknum then sum NETADV
I've removed the offset element because column i has blanks in the dates.
But still no joy. Any ideas to get it summed?
Cheers, again, Les
I have been using Weeknum for some time.
The second parameter is to define whether the week starts on Monday(2)
or Sunday(1)
The first of the year 2006 was a Sunday.
Entering =weeknum(1/7/2006,2) which is a Saturday in cell A2 produced
1. And I agree.
Entering 1/7/2006 in cell A1 and weeknum(A1,2) in cell A2 produced 2
???.
Entering 12/25/2006 which is a Monday, in cell A1 and weeknum(A1,2) in
cell A2 produced 53 ???.
Entering =weeknum(12/25/2006,2) in cell A2 produced 1. That is weird.
You would expect 52.
Am I going to be obliged to use an If... to produce the expected
result ?
Can you give me some light ?
Thank you,
Wayne
Hi,
I am using excel 2003 and I am having an issue with the weeknum formula.
37
9/8/2009
02:00:00 PM PST
#NAME?
9/8/2009
09:00:00 AM CST
The formula I have in column A is =weeknum(b1,2). B1 is formatted as a date. I don't know how 1 row can be correct and the next row incorrect. I have tried copying the formula, format painting the 2 columns, clearing and deleting both columns and reentering the formula.
At one point all the week numbers were correct. After each week is completed I hid the rows, and therefore, did not know the week numbers were in error.
I just recently did a paste link of the worksheet into a summary sheet for another purpose. Could that be the problem?
Thanks in advance for any help available!
Any suggestions?
hi guys,
please give me advice how to change weeknum in excel,
29 dec 2008 supposed to be week 52 but in my excel it returns to W53
thanks for helping me,
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)
So now that I've been able to transform my text value from this syntax:
Jun 2 2010 6:54AM
to this syntax:
Jun 2 2010
I am unable to get WEEEKNUM function to translate it into a week value using this:
=IF(I40="","",IF(WEEKNUM(B40)=53,1,WEEKNUM(B40)))
What am I missing?
Thanks!
Quote:
=J2/IF(YEAR(I2)>YEAR(H2),WEEKNUM(I2)WEEKNUM(H2)+52,WEEKNUM(I2)WEEKNUM(H2)+1)
I have this formula that I put into a macro on my computer and when other people run the macro on their computers it's giving a #NAME? Error any idea why? if so, do you know a fix?
Thanks in advance.
Hi all,
Just a small but annoying query.
I have a column of dates and to the right their week number using the formula =weeknum(rc[1],2). Ideally these week numbers would be values rather than a formula and I am able to use VBA to enter the formula and then copy and paste the answers as values.
My query is whether I could get the calculations to work in VBA so that the values go directly in to the workbook or whether I need to do what I am currently doing.
My current code is below to enter the formulas.
Many thanks for you time and help
Code:
i = Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To i  1
Rows(1).Find("Week Number").Offset(j, 0) = ("=weeknum(rc[1], 2)")
Next
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.
Is it possible to find out a weeknum from a certain date?
let say 11/3/08 and what weeknum it would be for 1/25/09
would be week 12
Hi all,
I'm missing something in this sum formula that I can't see.
=SUM(((IF(Data!$F$2:$F$10000=PINDESC!B2,IF(WEEKNUM(Data!$E$2:$E$10000,2)='YTD Summary  PL week on week'!$C$1,Data!$H$2:$H$10000,0)))))
I know its the IF(WEEKNUM(Data!$E$2:$E$10000,2)='YTD Summary  PL week on week'!$C$1
$C$1 is =(WEEKNUM(NOW(),2)1) and is fine.
The add in is checked and working fine.
I'd be grateful if someone could offer guidance for me.
Many thanks Lawdie
I have a table with all the dates of the year in column A and corresponding data in columns B on out. In order to sum the values for each month, I use the formula
=SUMPRODUCT(I$5:I$370,(MONTH($B$5:$B$370)=[#]))
where the [#] references the month I want to sum (e.g., 1=January, etc.). This formula works just fine. In order to do the same type of calculation by week, I entered the formula
=SUMPRODUCT(I$5:I$370,(WEEKNUM($B$5:$B$370)=[#]))
In this case, the [#] references a cell in which I have =WEEKNUM(TODAY()). This gives me the current week number and should give the other formula the value it needs to add up all the values in the current week. Instead, I get a #VALUE! error. Even if I just hard enter the week number, I still get the error.
Why will this formula accept the MONTH reference, but not the WEEKNUM?
I just noticed that Excel 2000
makes a mistake when calculating weeknumbers in 2005.
Could that be an Excelbug?
23122004 52 =WEEKNUM(C4;2) OK
24122004 52 OK
25122004 52 OK
26122004 52 OK
27122004 53 OK
28122004 53 OK
29122004 53 OK
30122004 53 OK
31122004 53 OK
01012005 1 WRONG Should be 53
02012005 1 WRONG Should be 53
03012005 2 WRONG Should be 1
04012005 2 WRONG Should be 1
05012005 2 WRONG Should be 1
06012005 2 WRONG Should be 1
07012005 2 WRONG Should be 1
08012005 2 WRONG Should be 1
09012005 2 WRONG Should be 1
10012005 3 WRONG Should be 2
11012005 3 WRONG Should be 2
Best regards,
Arien de Haan
Eindhoven NL
I have been trying to use WEEKNUM function within SUMPRODUCT function to do a multiple crieteria summing, without success. Can someone tell me why this does not work. The formula I am using is:
=SUMPRODUCT((WEEKNUM(A1:A33)=3)*(C1:C33="A"),(D1:D33))
I have the required Analysis ToolPak Addin installed and WEEKNUM() function is woring when used stand alone.
Thanks in advance.

