LinkBack URL
About LinkBacks
I need a formula for excel 2007 to calculate the week number if the week starts on a Saturday, I noticed that the Weeknum(A1,16) is not working for 2007
When I use Weeknum(A1,1) I get week 18 for 04/05/2013 and I need week 19
I need a formula for excel 2007 to calculate the week number if the week starts on a Saturday, I noticed that the Weeknum(A1,16) is not working for 2007
When I use Weeknum(A1,1) I get week 18 for 04/05/2013 and I need week 19
hi and welcome to the forumHoe gaan dit?
1st, there is no weeknum(a1,16)...weeknum() takes a 1 or 2
1...Week begins on Sunday. Weekdays are numbered 1 through 7.
2...Week begins on Monday. Weekdays are numbered 1 through 7.
I get 18 too, if you want to "fix" it, add 1
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks Fdibbins,
I've been using this forum for quite some time know, just never had to ask anything before.
If I add 1 my week starts on 05/05/2013 as week 20 and I need my week to start as week 19 on 04/05/2013
If this date :A1=04/05/2013 then the weeknumber should be B1=19
A2=11/05/2013 B2=20
A3=18/05/2013 B3=21
A4=25/05/2013 B4=22
Hope this explains it a little better
I have checked 3 different web sites, and they all confirm that 4/5 (Euro/SA date format) is week 19, not week 20. So if you want something other than that, use weeknum()+1
http://www.calendar-365.com/week-number.html
http://www.epochconverter.com/date-a...rs-by-year.php
http://weeknumber.net/calendar/england/2013-1
I ended up using this formula:
=WEEKNUM(A1,WEEKDAY(A1,1)-6)+1
It works great, just need to workout how Week 53 need to show as Week 1
week numbers.xls
If you are using Excel 2007 that will given an error if A1 is any day but Saturday so you might as well use FDibbins suggestion and useOriginally Posted by jacodtt
=WEEKNUM(A1)+1
which will always give the same results for Saturdays.
There are usually 2 elements which fully define how you want week numbers to work - start day (which in your case is Saturday) and definition of week 1 (WEEKNUM always starts week 1 on 1st Jan but next week starts on the first Sunday/Monday after that whereas ISO week numbers always have 7 days with week 1 possibly starting at the end of the previous year) - what's your definition?
This formula will give you results equivalent to =WEEKNUM(A1,16)......without using Excel 2010
=INT((A1-DATE(YEAR(A1),1,1)-WEEKDAY(A1+1))/7)+2
but that formula will give you 53s too (and even 54 in some years, e.g. 2016)
Audere est facere
Thanks Daddylonglegs,
Unfortunately the company I work for has the week starting on a Saturday and doing projects its difficult stating the progress if the week number is not correct.
I will use this formula and just change Week 53 to Week 1.
Thanks
You try this
Please Login or Register to view this content.
This formula is working great,
thank you so very much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks