WEEKNUM function

Question

I have a large data where transaction dates need to be converted to the week number of year they pertain to. The condition now is

1. Week starts on Sunday,hence ends on Saturday
2. Financial year starts on 1st October, ends on 30th September

Keeping the above in mind, the formula should return the value. Can it be worked around weeknum formula or something else ?

Answer 1

Thanks Mike. The subject formula returns "1" all over the data, regardless of actually when it falls in October/ December/ March/ etc..

Hi,

If it does that then it's because you may have something in the cell the formula is referencing that isn't being recognised as a date. Below is a sample of what the formula returns for me. What exactly do you have in the cells? Post some samples.

Are you sure that calculation in your workbook is set to automatic? Try formulas tab, calculation options and ensure 'Automatic is selected.

Date	Week No
25/09/2014	52
26/09/2014	52
27/09/2014	52
28/09/2014	52
29/09/2014	52
30/09/2014	52
01/10/2014	1
02/10/2014	1
03/10/2014	1
04/10/2014	1
05/10/2014	1
06/10/2014	1
07/10/2014	1
08/10/2014	2
09/10/2014	2
10/10/2014	2

Answer 2

Thanks Mike. The subject formula returns "1" all over the data, regardless of actually when it falls in October/ December/ March/ etc..

Answer 3

Hi,

So in effect the week starting on a Sunday isn't relevant, week 1 starts 1/10 no matter what day it is. Try this for a date in A1. The formula can be dragged down

=MOD(INT((A1-DATE(YEAR(A1)-(MONTH(A1)<=8),10,1))/7),52)+1

Edit... formula edited. see below.

Answer 4

One problem is to determine how to define Week 1.   One standard is that all weeks are seven days long, and Week 1 of the year is the first week that contains a Thursday (that standard ISO8601, also has the week starting on a Monday).  So sometimes the last few days of the previous year are in the subsequent year; other times the first few days after Oct 1 are in the previous year.

But other conventions can be used.  Including short weeks at the start/end of the year.

So how do you define Week 1?

That precisely is the constraint. Now lets take example of "01st October 2014"

This date falls on Wednesday, which according to our organisation is the first day of financial year. Now even though technically , working week starts on a Sunday, which 5th October 2014 would be first Sunday of the financial year; we will need to ignore that in our case.

So to set the rule, here it goes

1. Week # 1 starts on 1st October, regardless of whichever day it falls on
2. count 7 days from first day of first week. All such days will be indicated, falling within week # 1.
3. Start week # 2
4. Repeat steps as point # 2 above to calculate second week. All such days will be indicated, falling within week # 2.
5. so on
6. Last week of the year concludes on 30th September 2015

Answer 5

One problem is to determine how to define Week 1.   One standard is that all weeks are seven days long, and Week 1 of the year is the first week that contains a Thursday (that standard ISO8601, also has the week starting on a Monday).  So sometimes the last few days of the previous year are in the subsequent year; other times the first few days after Oct 1 are in the previous year.

But other conventions can be used.  Including short weeks at the start/end of the year.

So how do you define Week 1?