week number based on year start 01/10

**tony0710** · 01-09-2008, 07:01 PM

Dear All

My finacial year starts on the 1st of October of each year and ends 30th of September each year.

My week is from Monday to Saturday - though for calculation ease: lets say that my week is from Monday to Sunday.

I need to calculate the week numbers of a date based on the above two criteria.

I understand that I need to ascertain the day of the start date first i.e: did 01/10/07 fall on Monday, Tuesday etc. - In fact it was Monday!

so for my year 07/08:

Week 1 was 01/10/07 to 07/10/07

Week 2 was 08/10/07 to 14/10/07

ETC

So in the attached worksheet - what formula can I use to populate column D - by using the data in column E?

I.e. All I want to do, is input date into E and B, C, D will be calculated automatically. It would be great of excel could populate A - aswell ;-)

All help will be much appreciated.

**daddylonglegs** · 01-09-2008, 07:12 PM

All your dates appear to be in the previous year, 2006/2007. In that year 2 October was a Monday, in that case, or when, say, first Monday is the 5th how would your numbering go? Would the last week of the previous year extend into October, would week 1 start on the 5th?

How are the quarters defined?

**oldchippy** · 01-09-2008, 07:15 PM

It may also be worth while taking a look here on Week numbers

http://www.cpearson.com/excel/weeknum.htm

**tony0710** · 01-10-2008, 04:31 AM

The sample supplied was just an hypothetical example - I randomly took a list of dates; but the concept is exactly the same as to what I require - BASICALLY I want to input the date, and would like excel to automatically work out the rest of the contents in each cell in that particular row.

My Year starts from the 1st of October of each year to the 30th September on the following year.

Re-thinking about the working week:

It is from Monday to Saturday that I actually work; but if any purchases are done on a Sunday; then they are added to the next working week. So ideally for the calculation of the week number the week should start from Sunday and run through to Saturday. (I was wrong in my original posting).

So with this in mind:

Year is 1st October to 30th September
Week is Sunday to Saturday
Quarters are defined as:
Q1: 1st October to 31 December
Q2: 1st January to 31 march
Q3: 1st April to 30 June
Q4: 1st July to 30 September

NOTE: at the moment in time I input the quarters manually.

So if 1st October falls on Friday (for argument sake) week 1 would be for 2 days only i.e: Fr/01/10 and Sa/02/10 - then week 2 would start from Sunday 03/10.

There will be some years that will have 52 weeks and others that will have 53 weeks - depending on the what day 30th September falls on.

I've looked at cpearson website - but I don't understand how to change the formula to suit my requirements i.e.: start or financial year to be 1st October. Can you explain how his formula is constructed - perhaps then I can understand it's working and adapt it to my needs.

Thanks and sorry for the confusion.

**Dav** · 01-10-2008, 06:17 AM

If the date is in cell A1 perhaps something like the following

=TRUNC((IF(MONTH(A1)>=10,A1-DATE(YEAR(A1),10,1),A1-DATE(YEAR(A1)-1,10,1))+WEEKDAY(IF(MONTH(A1)>=10,DATE(YEAR(A1),10,1),DATE(YEAR(A1)-1,10,1)))+6)/7)

Does that work

Regards

Dav

**daddylonglegs** · 01-10-2008, 06:46 AM

Hello Tony,

What you describe is similar to how WEEKNUM function works in Excel but with the year starting on 1st October instead of 1st Jan. As with weeknum it is possible to have a week 54 in some circumstances, e.g. year 2011/2012 has 366 days and starts on a Saturday therefore week 1 has only 1 day and there needs to be a week 54 which has only 1 day too (Sun 30th Sept 2012)

This formula in D2 copied down will give the correct week number

=INT((E2-WEEKDAY(E2)-DATE(YEAR(E2+92)-1,10,1))/7)+2

format as general

For quarters/years try this formula in A2 copied down

="YR "&TEXT(EDATE(E2,-9),"y/")&TEXT(EDATE(E2,3),"y Q")&LOOKUP(MONTH(E2),{1,4,7,10;2,3,4,1})

Note: EDATE is an Analysis ToolPak add-in function

edit: without EDATE you could try

="YR "&RIGHT(YEAR(E2)-(MONTH(E2)<10),2)&"/"&RIGHT(YEAR(E2)+(MONTH(E2)>9),2)&" Q"&LOOKUP(MONTH(E2),{1,4,7,10;2,3,4,1})

**NBVC** · 05-25-2011, 07:47 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.