week number based on year start 01/10

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?

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:

1. 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.

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

Please Login or Register  to view this content.

Does that work

Regards

Dav

Dear Daddylonglegs,

That has worked a treat Thanks for your help. Brill!!!

How would the formulas change, if the start date was 1st June INSTEAD of 1st October?

Your help will be much appreciated - as it will help me to understand as to how to construct the formula. Then I won't have to disturb you, I'll be able to modify it myself!

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

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

Where do the numbers in red come from?? What do they mean??

Thanks again - a brilliant solution.

Hello Tony,

As I said above, what you asked for is similar to WEEKNUM. I use this formula to give the same result as WEEKNUM

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

where the date is in E2

In that formula E2-WEEKDAY(E2) gives the Saturday before E2 and

=DATE(YEAR(E2),1,1) gives the start date of the current year, i.e. 1st Jan

When you subtract the first part from the second you get the number of days between these, and dividing by 7 and taking INT gives you the number of weeks difference rounded down. We actually need that rounded up so we add 1 and because we only calculated up to the previous Saturday we need to add another 1, hence +2 at the end.

To modify that formula for 1st October the date part needs to calculate the previous 1st October, e.g. if E2 is 2nd October 2007 then the year start date is 1-Oct-2007 but if the date is 29th Sept 2007 the start date is the previous year 1-Oct-2006

This part of the above formula I used gives that date

=DATE(YEAR(E2+92)-1,10,1)

92 is the number of days between 1st October and 1st January of the next year so if E2 is a date in 2007 then YEAR(E2+92)-1 will give 2006 as long as E2 is January to September, if E2 is October or later then E2+92 will give a date in the next year so YEAR(E2+92)-1 will give 2007.

This means that if you want to calculate this for June 1st formula would be

=INT((E2-WEEKDAY(E2)-DATE(YEAR(E2+214)-1,6,1))/7)+2

Note: you need to calculate the number of days from 1st June to 1st Jan in the following year because this number of days is fixed (can't contain a leap day). If you want to start on 1st Feb you'd go back the other way, e.g.

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

The second formula uses some similar logic, the -9 in EDATE differentiates between dates before or after 1st October (if you deduct 9 months from a date in October or later the result is a date in the current year, if you deduct 9 months from any other date you'll get a date in the previous year). The formula adjusted for 1st June would be

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

The LOOKUP part is probably more transparent written like this

=LOOKUP(MONTH(E2),{1,3,6,9,12},{3,4,1,2,3})

The month number is looked up in the first array and matched with the greatest value less than or equal to the lookup value and then the corresponding number is returned from the 2nd array.

Example

E2 is 5th May 2007
MONTH(E2) is 5

This is looked up in the array {1,3,6,9,12}

The greatest value that's less than or equal to 5 is 3, the 2nd value in the array so the formula returns the 2nd value in the 2nd array, i.e. 4.

so if month is 1 or 2 quarter is 3, if month is 3, 4 or 5 quarter is 4, if month is 6, 7 or 8 quarter is 1......etc.

1st array must be in ascending order

wow Thanks for taking the time to explain how you got the formulas - will need to get my head around them so will take time.

In the mean time - got another query for you based same problem:

Start of first year: 04/06/07
End of first year: 30/03/08
Q1: 04/06/07 to 30/09/07
Q2: 01/10/07 to 31/12/07
Q3: 01/01/08 to 31/03/08

Then the second year and all subsequent years will be:
Start date: 01/04 to 31/03
Q1: 01/04 to 30/06
Q2: 01/07 to 30/09
Q3: 01/10 to 31/12

What's the best way of dealing with the quirky first year, with the first quarter being 4 months long and then reverting back to 3 months

All help will be much appreciated.
Q4: 01/01 to 31/03

Dear Daddylonglegs

That formulas were great - I've studied them, and amazingly have been able to understand what you have done - so thanks for explaining it.

Many regards

Tony

I am using this current formula to calculate the week number, though what I need to include is the year as well. I am using a getpivot on another sheet to compare to =now.

=INT((C138-SUM(MOD(DATE(YEAR(C138-MOD(C138-2,7)+3),1,2),
{1E+99,7})*{1,-1})+5)/7)

What I am looking for:
01/2008
Week/Year

Any help in the right direction would be great!

Dear daddylonglegs,

I've come across your your solution that I must say works great. However, I would like the week to start on Monday instead of Sunday, could you help please.