Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Weeknum() Question

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

what happened to week 1?

  A B C 1   Sunday Sunday 2 Week begin Date 27-Dec-09 03-Jan-10 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

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
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

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: 7-Sep-2010 (Monday) to 3-Oct-2010 (Sunday) should be WEEK39 for me, but result i got for 3-Oct-2010 (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("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


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

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 on-call 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 add-in 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 Ex-Thanks 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 12-14 i want all the dates in between (including the first and last date) to say "No Work" also the same with AL 16-18 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:
=(A1-WEEKDAY(A1)-DATE(YEAR(A1+7-WEEKDAY(A1)),1,1))/7+1
However, here are issues with the results:
01-01-2000 (Sat) - Week 0 (correct)
02-01-2000 (Sun) - Week 1 (correct)
31-12-2000 (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.
07-01-2001 (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((A1-DATE(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 mid-week, 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.

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** 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'!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


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(date1-date2)/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