
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
what happened to week 1?
A
B
C
1
Sunday
Sunday
2
Week begin Date
27Dec09
03Jan10
3
Week number
53
2
Spreadsheet Formulas
Cell
Formula
C3
=WEEKNUM(C2)
Excel tables to the web >> Excel Jeanie HTML 4
cell C3 should = 1 as it is the first Sunday in 2010
Martin
Similar Excel Video Tutorials
Random Num 1 100 Not Divisible By 3
 See how to create random numbers between 1 and 100 that are not divisible by 3 with a large array formula. See these functions in this video: The ...
Excel VBA code From Internet
 Learn about: 1.Formatting a simple Static Report (Common Macro task in the working world) 2.Posting question to Mr Excel Message Board to ge ...
Similar Topics
Hi,
I am trying to convert a date into week number by using WEEKNUM function. I would like Monday is the first day of week and Sunday is the last day of the week.
i.e: 7Sep2010 (Monday) to 3Oct2010 (Sunday) should be WEEK39 for me, but result i got for 3Oct2010 (Sunday) is WEEK40.
I have attached a weekbook along. Please kindly advice
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 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(H391),IF(F39
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
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
Hi Experts
How can i Divide my Monthly Target into Week Wise
But, For 1st Week Not to Include Sunday
For 2nd Week Not Include Saturday and Sunday
For 3rd and 4th Week Not Include Sunday
Below is the Example of my Data
How can i Calculate the Target of "AHMEDABAD"
Sheet3
*
K
L
M
4
Campus
FS
Grand Total
5
AHMEDABAD
20
20
6
AURANGABAD
24
24
7
BANGALORE
132
132
8
BHUBNESHWAR
32
32
9
CHANDIGARH
36
36
10
CHENNAI
104
104
11
DELHI
176
176
12
GOA
32
32
13
HUBLI
24
24
14
HYDERABAD
84
84
15
INDORE
48
48
16
JAIPUR
52
52
17
JAMMU
20
20
18
KATHMANDU
20
20
19
KOLKATA
124
124
20
LUCKNOW
56
56
21
MUMBAI
224
224
22
NAGPUR
48
48
23
NASIK
24
24
24
NOIDA
76
76
25
PUNE
92
92
26
SURAT
0
0
27
TRIVANDRUM
16
16
28
VARANASI
28
28
29
VIJAYWADA
16
16
30
Grand Total
1508
1508
31
*
*
*
32
AHMEDABAD
*
33
Weekly
Target
*
34
WEEK 1
* * * * * * * * * * * *
*
35
WEEK 2
* * * * * * * * * * * *
*
36
WEEK 3
* * * * * * * * * * * *
*
37
WEEK 4
* * * * * * * * * * * *
*
38
Total
* * * * * * * * * * * *
*
Spreadsheet Formulas
Cell
Formula
L38
=SUM(L34:L37)
Excel tables to the web >> Excel Jeanie HTML 4
I have a sheet that shows our oncall rotation.
B
C
2
Schedule Starting Date:
3
Week Start
Week End
4
Sunday, October 31, 2010
Saturday, November 06, 2010
5
Sunday, November 07, 2010
Saturday, November 13, 2010
6
Sunday, November 14, 2010
Saturday, November 20, 2010
7
Sunday, November 21, 2010
Saturday, November 27, 2010
8
Sunday, November 28, 2010
Saturday, December 04, 2010
9
Sunday, December 05, 2010
Saturday, December 11, 2010
10
Sunday, December 12, 2010
Saturday, December 18, 2010
11
Sunday, December 19, 2010
Saturday, December 25, 2010
12
Sunday, December 26, 2010
Saturday, January 01, 2011
What I'd like to do is have people be able to enter the date into C2 and have the sheet automatically eliminate any dates that are no longer valid. So, for example if they put 11/15/10 into C2, it would eliminate row 4 and 5 so that row 6 is at the top.
Any suggestions?
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?
Dear Excel gurus, what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?
For example (Sunday being the first day of the week):
January 5th 2008 = Week 1 of January
January 6th 2008 = Week 2 of January
February 2nd 2008 = Week 1 of February
February 3rd 2008 = Week 2 of February
WEEKNUM perfectly calculates this, but it is applicable for the whole year.
Hi all.
I'm running a spreadsheet that manages lessons in a certain course.
I have a cell that contains the date of the first day in the course, which changes, of course, from one course to another. Note that the course can begin in any day and not necessarily on the beginning of the week.
In the list of the lessons, each lesson has its date.
I'm looking for a formula that would give me the week number of the lesson, from the beginning of the course (column G in the attached file).
For example, if the course had started on Tuesday, September 18th, a lesson on Sunday, September 23rd would be on the second week of the course.
I cannot use the function "weeknum" because it is a part of the Analysis ToolPak addin which I cannot install.
Thanks a lot.
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)
hi
I run a number of spreadsheet that use the '=weeknum(today(),2)' function in excel.
Now in the UK we are currently week 6 (forget the Sunday / Monday rule) but excel is returning week 7.
I am after a way to rectify this with out using a 1 solution that I will have to change next year.
Look forward to hearing from you
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 All,
Happy Thanksgiving to all that it applies.
Happy ExThanks to all that it applies.
Now We Move Onward,
i have a list of dates in table 2(it does not matter the year) i just want it to refer to the month and day. so i want when the date found (2nd table) is the same (table 1) than i want colum Ai to say "No Work" (Example Not Shown)
AL 1214 i want all the dates in between (including the first and last date) to say "No Work" also the same with AL 1618 and if i change a date (2nd table) i want it to update in the first table
Sheet6
AF
AH
AI
2
11/23/2009
Monday
Beginning of Week
3
11/24/2009
Tuesday
Work Day
4
11/25/2009
Wednesday
Middle Of Week
5
11/26/2009
Thursday
Work Day
6
11/27/2009
Friday
End Of Week / Pay Day!
7
11/28/2009
Saturday
Day Off
8
11/29/2009
Sunday
Day Off
Spreadsheet Formulas
Cell
Formula
AH2
=TEXT(AF2,"DDDD")
AI2
=IF(AH2="Monday","Beginning of Week",IF (AH2="Wednesday","Middle Of Week",IF (AH2="Friday","End Of Week / Pay Day!",IF (OR (AH2="Tuesday",AH2="Thursday") ,"Work Day",IF (OR (AH2="Saturday",AH2="Sunday") ,"Day Off","") ) ) ) )
Excel tables to the web >> Excel Jeanie HTML 4
Sheet6
AL
AM
2
1/1/2009
39814
3
1/31/2009
39844
4
2/1/2009
39845
5
2/14/2009
39858
6
2/25/2009
39869
7
6/17/2009
39981
8
7/4/2009
39998
9
9/11/2009
40067
10
10/31/2009
40117
11
12
11/23/2009
40140
13
Through
14
11/27/2009
40144
15
16
12/23/2009
40170
17
Through
18
1/3/2010
40181
Spreadsheet Formulas
Cell
Formula
AM2
=AL2
Excel tables to the web >> Excel Jeanie HTML 4
Column AM is formatted as "General" Please Ask Any unanswered questions!
Using Windows Xp Home Edition on Sp2 With Microsoft Excel 2003
Thanks!
Hi,
I have read numerous postings on how to calculate week numbers in a year, but none seems to work correctly for what I need.
I need to calculate week numbers from years 2000 to 2030 so a lot of leap years are encompassed. The week runs Sunday to Saturday and Week 1 in any given year should start on the first Sunday of that year.
The closest formula I have found to what I need is:
=(A1WEEKDAY(A1)DATE(YEAR(A1+7WEEKDAY(A1)),1,1))/7+1
However, here are issues with the results:
01012000 (Sat)  Week 0 (correct)
02012000 (Sun)  Week 1 (correct)
31122000 (Sun)  Week 1 (incorrect, should be Week 0)
This should not be Week 1 as this date is still in 2000. This is a leap year and I am wondering whether this is part of the problem.
07012001 (Sun)  Week 2 (incorrect, should be Week 1)
This should be Week 1 as this is the first Sunday of the year 2001.
I don't mind if the Week 0 dates become Week 53/54, but I do need Week 1 to start on the first Sunday of each year.
Any help would be greatly appreciated!!
Thanks,
Jude
Found this formula
INT((A1DATE(YEAR(EDATE(A1,3)),7,1))/7)+1 while searching this forum. http://www.mrexcel.com/board2/viewto...hlight=weeknum
Thanks to Barry Houdini for the same. My year starts from July 1 and the first week starts from the first Sunday. Above formula is giving the results but the problem is that the above formula treats Saturday as the first day instead of Sunday. Any help in modifying the above formula please?
Thanks
Chari
I need help with a formula. I have a certain list of dates, by day...and I need them listed by "week ending" (where a week "ends" on a Sunday).
For example, for the week ending 5/23, I might have the dates:
5/23/10 (Sunday)  the formula should pull 5/23/10
5/22/10 (Saturday)  the formula should pull 5/23/10
And if the date was earlier...say 5/11/10...it should return "5/16/10" because that is the Sunday of the week that contained 5/11/10.
I know there is a way to do this with the Weekdays function combined in a formula, but I don't remember what it is. Does anyone know?
Thanks!
SundayST
Hi All,
I have a cell that contains a date in the format dd/mm/yyyy
For the sake of this exercise, the week must begin on Monday.
I am trying to create a formula that can read this date and interpret which week within the month the date corresponds to. For example, 01/03/2010 would belong to week 1 of March.
There is however, a twist..!
The months must be broken down into 4 weeks, no more, no less! For this to work, the number of days in weeks 1 and 4 must be flexible. For this example, let us say that week 1 begins on a Tuesday (thus shortening the working week), Tuesday  Sunday must be added to the following week, therefore leaving you with week 1 spanning Tuesday 1st  Sunday 13th.
Week 4 must be able to expand and contract to suit the remaining days after week 3. If the month ends midweek, Wednesday 31st for example, week 4 must span Monday 28th  Wednesday 30th.
If there are more than 7 days left in month following the end of week 3, week 4 needs to be able to expand to accommodate all of these days. An example of this may be week 4 spanning Monday 23rd Tuesday 31st.
Any help would be greatly appreciated! VBA or formula based solutions welcome.
Cheers,
OG_Flynn
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
Hey All,
Sheet6
BH
BI
BJ
2
Monday
8
Tuesday
3
Tuesday
14
Tuesday
4
Wednesday
25
Sunday
Excel tables to the web >> Excel Jeanie HTML 4
i need help with a formula that will take the day of the week and add the number and give me the day of the week that many days later.
i want it to start counting the next day of the value in column BH
ive tried text(day(BH2),"DDDD")+BI2 i get a value error
Any Question Please Ask!
Thanks!
Hi,
I am running some tests on machinery and need to work out the number of hours the machinery has run per week. I need to do this on a Monday  Sunday based week. I can use the WEEKNUM function when the data fits neatly into a date range which is within one week and then SUMIF using the relevant weeknumber, but the problem I am having is when the test runs from say a Thursday to a Tuesday and spans two week numbers.
I have columns with 'start date/time' and 'stop date/time' in the format '01/12/2010 15:27'. Then a column which works out the total time run between the two dates.
Hi All,
I pretty new to Excel and have been trying to solve this problem for days. I think I'm nearly there but am a bit unsure of the syntax I want to use.
Here is my problem:
I have a column of dates (1st column) and I want to do a VLOOKUP within an array ( E14:I24) to extract the whole row of any dates that fall into a particular week number.
****** httpequiv="ContentType" content="text/html; charset=utf8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9">
Deliverable Date (Planned)
Deliverable Date (Actual)
% Complete
Slippage (days)
16/01/2010
16/01/2010
100
0
So far I have achieved extraction of whole rows by specifying the date of the rows  see the following formula. However, this does not return all dates within a particular week number e.g all dates that fall into week 3:
{=VLOOKUP(DATEVALUE("16/01/2010"),E14:I24,{1,2,3,4},FALSE) }
I know how to give a cell (B11) todays date by:
=TODAY()
and another cell the week number by using the following formula that looks at the date in (B11):
=WEEKNUM(B11,2)
So I've tried to join these formulas together so it looks something like the following but obviously the syntax is incorrect:
{=VLOOKUP(WEEKNUM($E$14,1) = WEEKNUM($B$11,1),E14:H24,{1,2,3,4},FALSE)}
Can someone help me this please. I've exhausted my knowledge of Excel. Any help would be appreciated.
Many thanks
Shanz
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
Hi,
I need to find the date, preferably of the sunday, in a given week.
For example, I have as input week 27 2008, I'd like to find the date of the sunday in that week.
I'm actually trying to find the number of weeks between two such sets of input, for example week 27, 2008 to week 12, 2010.
I figured if I could find the dates, I could simply use
difference=int(date1date2)/7
but if anyone has a better idea, I'd like to hear about it.
A
I've created a rudamentary function to calculate the start date of the week for the week number that is passed. Problem is, since this year (2009) started on a Thursday I need to subtract four days. Obviously this won't work come next year since it starts on a Friday, I was going to write this elaborate code to calculate the offset of Sunday from 1/1/yyyy but figured I should ask if anyone else had any better ideas.
do you?
Code:
Function WeekNumToDate(weekNum As Integer)
WeekNumToDate = DateAdd("d", 7 * weekNum  4, "1/1/2009")
End Function

