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


Advertisements


Free Excel Forum

Weeknum Function

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

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

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
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
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
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

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.




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.

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

Please advise.

Thanks

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


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

I have never used =WEEKNUM before and have it ona spreadsheet. The problem is
the formula used is:

=WEEKNUM(DATE(theyear,1,1)-7)

but it returns a value of

#NAME?

What it should be returning is 1.

I have looked on the internet for help but to no avail.

Please could you get back to me this

Thx


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.

Thanks in advance.


I have Analysis Toolpak checked.

What should I look at next.

Never had this problem before.

Here's my syntax:

=IF(J2="","",IF(E2="Project",IF(WEEKNUM(AJ2)=53,1,WEEKNUM(AJ2)),IF(WEEKNUM(AT2)=53,1,WEEKNUM(AT2))))

What's going on here?...


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?


WEEKNUM starts a new week on Sunday. I need it to start on a Monday. Any ideas on how to adjust this? (I don't know VBA or other code...so I just need formulas).

Is there a better formula that below to solve this problem?

=IF(I39="Sun",WEEKNUM(H39-1),IF(F39

Hello hello,

Freshly registered on this forum, I'm here to find a little support with some of Excel functions I'm still not used too.. I'm very new in VBA so please, accept per advance my excuses if I'm not 100% clear with the terminology..

Well.. I got a file from my boss that is linked to an online table. This table contains customer contacts infos such as account number, activation date & time, etc, etc.. (1 contact = 1 line)

Now (for "quick filtering reasons" he said), my boss "needs" to have also in the sheet the week number for every contact we had..(as well as Month & Year)
and as the list grows up everyday with new entries, I thought I could use VBA..
so I wrote a little something that uses the date&time column to have the wanted infos added on every line :

Code:

 Private Sub Worksheet_change (Target As Range)

Set MyPlage = Range("G2", Range("G65536").End(xlUp))
For Each cell In MyPlage
        
        cell.Offset(0, 9) = "=MONTH(RC[-9])"
        cell.Offset(0, 10) = "=year(RC[-10])"
        cell.Offset(0, 11) = "=weeknum(RC[-11])"
        
       Next
End
End Sub 


It works fine with the Month & the year, both return the correct value in the right cells.

But I can't find a way to have it working with the weeknum one.. it returns me a 4 digit value...

For example, with "23.12.2009 09:07" (cell format = custom dd.mm.yyyy hh:mm), the weeknum returns me the value 5740 (when cell format is general, and "01.07.1905 00:00" when in date format) when it should returns me 52!

my only explanation is that it calculates the amount of weeks from 01.01.1900 to 23.12.2009 but yeah... it's not exactly what I need..

Does anyone know how I could solved this issue ? or how I could pass by ?

Thanks in advance for your answer,
I hope you'll understand my question, I'm not that used to write "technical" things in english..

merci merci,
steve




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.