• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

creating week number not starting on 1 Jan [SOLVED]

C

Curious George

New Member
Hi All,


I'm a newbie to all this, so forgive the inexperience!


I'm wanting to identify the week number of a date, but I want to start the week number count from 6th April to 5th April the following year. The week number would commence on a Monday (day = 2)...


Help! Any clues?
 
Hi Curious George


I had a similar problem which I solved by using one of the following examples that can be found here http://excelworks.co.uk/default.aspx?page=30200


I chose the Download calendar with custom week numbers (xls 330KB) which enabled me to set the calendar to start on a specific month and a specific day. Hope this helps a little
 
Hi, Curious George!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Assuming your date is in cell A1 try this:

=NUM.DE.SEMANA(A1;11)-NUM.DE.SEMANA(FECHA(2013;4;6);11)+1+52*(NUM.DE.SEMANA(A1;11)<NUM.DE.SEMANA(FECHA(2013;4;6);11)) -----> in english: =WEEKNUM(A1,11)-WEEKNUM(DATE(2013,4,6),11)+1+52*(WEEKNUM(A1,11)<WEEKNUM(DATE(2013,4,6),11))


Regards!
 
Thanks SirJB7 for your help, I knew someone would be along to help further. However your formula [=WEEKNUM(A1,11)-WEEKNUM(DATE(2013,4,6),11)+1+52*(WEEKNUM(A1,11)<WEEKNUM(DATE(2013,4,6),11))] doesnt work for me.
 
Hi, ianamck!

It does, and not only for me, check this file:

https://dl.dropboxusercontent.com/u/60558749/creating%20week%20number%20not%20starting%20on%201%20Jan%20%28for%20Curious%20George%20at%20chandoo.org%29.xlsx

Regards!
 
appreciate the help, but so you understand. I typed the formula exactly as you had written it and once hitting return it gives a #NUM! error. When checking your worksheet and inspecting any cell with the WEEKNUM formula in (I.E. F2) and pressing return (enter)after checking out your solution the same happens #NUM! error. Must just be something I am doing.


Many thanks though
 
Hi, ianamck!

What Excel version are you using? It works in 2010+ versions, if you're using 2007- replace the the 2nd argument of WEEKNUM function from 11 to 2.

Regards!
 
SirJB7 you beat me to my own answer. As ever you are a superstar I am currently a 2007 user and so a change from argument type 11 to 2 did solve my problem and has allowed me to improve a solution I already had in place. A quick visit to my old friend http://www.excelfunctions.net/ shows quite clearly that only option 1 and 2 are available in 2007 and earlier. Thank you as always and keep up the great support!!
 
Hi, ianamck!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Hello SirJB7 & Ianamck,


Wonderful thanks for your responses (and SirJB7, I will definately search around the forum site for topics!)


I've looked at the formula and it works, however, it is not showing the correct "week number" for the dates before 06 April (when my week number sequence starts from)


For example: 01/04/2013 formula shows wk1, whereas I expected it to show wk52 (as it is the last week in the previous financial year). The same goes for the dates 02/04/2013, 03/04/2013; 04/04/2013; 05/04/2013.


The same happens at teh end of the current year where 31/04/2014 has the formula resulting in wk1, but I expected it to show week52.


Kindest regards!
 
Hi ,


Can you check this file to see if the calculations are correct ?


https://www.dropbox.com/s/y2mqrw64bwnmelb/George_Book1.xlsx


Narayan
 
Hi, Curious George!


About previous dates to April 6th, quoting from 1st post "The week number would commence on a Monday" the 1st week was considered to be that which starting on Monday contained April 5th.


31/04/2014? I assume you tried to mean 31/03/2014. And then again the same consideration: 1st week as it contains April 6th and starts on Monday March 31st.


If that not what you wanted please elaborate and rectify your initial specifications.


Regards!
 
Curious George even I am a little confused with what you are now requiring as is SirJB7. You have had some excellent answers from two great Excel Ninja's. However I understand the frustration you can sometimes feel when you are trying to find an answer. In you original post you wanted week numbering from the 6th of April to the 5th April each year using Monday as your start day of the week. using those dates for 2013 would suggest 6th April 2013 is actually in week 52 as it is a Saturday. Which means the first Monday of the new fiscal year would be 8th April 2013 = Week 1. If you want to tweak that so 6th April 2013 is actually in week 1 of 2013 and still have Monday as your first day of the week that would automatically mean the 1st to 5th April would also be in week 1. Does that make sense to you. I do have one further formula you could use that is not my own but may help you.


=INT((A4-LOOKUP(A4,DATE(YEAR(A4)-{2,1,0},4,06)-WEEKDAY(DATE(YEAR(A4)-{2,1,0},4,06-2))+7))/7)+1


This formula uses the 6th April each and every year as your first possible date of the fiscal year and calculates where the first Monday is. Then gives you the appropriate week number. Bearing in mind what I have pointed out above this would put 6th April 2013(a Saturday in Week 52)The first Monday on or after the 6th April is of course 8th April which is week 1


Hope this helps and if we are all missing the point maybe restating your question might help us find you an answer
 
Hi Ian ,


I think the question is fairly clear , even if OP's posts may be confusing.


1. The year begins on April 6 , and runs from April 6 of one year to April 5 of the next year.


2. So April 6 will always be in week 1 , while April 1 till April 5 will be week 52 or week 53 , depending on the calculations ; April 1 till April 5 can never be in week 1 , since they are a part of the previous year.


3. The week changeover is on a Monday ; thus if April 6 is a Saturday , April 6 and April 7 will be in week 1 ; week 2 will start with April 8. If April 6 falls on a Monday , it will be in week 1 , and week 2 will start with April 13.


Narayan
 
@ianamck!

Hi!

Absolutement d'accord, monsieur (Absolutely agree, Mr).

Regards!


@NARAYANK991

Hi!

I disagree: "The week number would commence on a Monday (day = 2)", so week 1 is the week starting on Monday which contains April 6th. For year 2013 April 1st-5th are in week 1.

Unless OP changes its original definition.

Regards!
 
Well with some help from elsewhere (Kudos goes to daddylonglegs of http://www.excelforum.com/) I have a solution that falls in line with what you believe to be the criteria NARAYANK991 so either way hopefully Curious George will have his answer.


=INT((A1-DATE(YEAR(A1+270)-1,4,6)-WEEKDAY(A1-1))/7)+2


noting that A1 has the target date in.
 
Hi, ianamck!

I was waiting for any life sign from Curious George so as to update the formula regarding the new specs but it seems as either its curiosity has been satisfied or he's experiencing connectivity issues.

So thank you very much for doing the other part of the job, I appreciate it and I think that the OP will do it as well when and if he read this topic again.

Regards!
 
Greetings from Planet George... apologies for being offline, I suffer a little from hayfever, and as summer has finally arrived in England, so to has the pollen! Bu I shall not complain!


I've looked at Ianamck's new formaula as all I can say they you folks are truely AWESOME Ninja's.


Thank you very much, you've made my life easier!


Best regards to all
 
SirJB7 said:
Hi, ianamck!

It does, and not only for me, check this file:

https://dl.dropboxusercontent.com/u... Jan (for Curious George at chandoo.org).xlsx

Regards!
Click to expand...

The answer above is great, but why is the date in the DATE part of the formula 2013,4,6? Basically, I want to understand how to customize this formula to fit several dates whenever I need it. I'm trying to work the above formula to fit with the start date being 4/25/16, and when I reference the formula in the link above to cell A1 being 4/25/16, I get 5, not 1.
 
evanlamarr88 said:
The answer above is great, but why is the date in the DATE part of the formula 2013,4,6? Basically, I want to understand how to customize this formula to fit several dates whenever I need it. I'm trying to work the above formula to fit with the start date being 4/25/16, and when I reference the formula in the link above to cell A1 being 4/25/16, I get 5, not 1.
Click to expand...
Hi ,

The forum member , who uploaded the workbook you are referring to , is no longer active on this forum.

Can you start a new thread , and post your problem description in all details ?

What will be the input data , what is the expected output , and what is the logic to derive the output from the input ?

Narayan
 
You must log in or register to reply here.
Back
Top