Calc weeknumber based on company's financial year

[mplees]

Hi,

Hope someone can help me out with this problem - which is a problem with two parts.

Firstly, I need a formula that will calculate the week number based on my company's financial year. The financial year always starts on a Sunday, & in the current year (2007/2008), week 1 commenced on Sunday, September 16th, and will end on Saturday, September 13th 2008. The next financial year will then commence on Sunday, September 14th 2008. It appears that the financial year has 52 weeks. I don't have access to the analysis toolpak, so really need a formula that doesn't use this feature.

My second problem is that I need to be able to calculate a period based on the date or week number. There appears to always be 4 weeks in each period, and in this financial year there are obviously 13 periods. P1 starts Sept 16th & ends Oct 13th, P2 starts Oct 14th and ends Nov 10th, & so on. Is there a way to construct a formula that will calculate the period?

All help most gratefully received!

Kind regards,

Mark

 

[barry houdini]

Hello Mark,
I assume that the financial year can’t always have 52 weeks otherwise, eventually, your year start will be in March! What’s the rule for when the financial year starts each year? Assuming it always starts on the first Sunday on or after 10th September, for instance, use this formula to determine the week number, where date is in A2
=INT((A2-WEEKDAY(A2)-DATE(YEAR(A2+DATE(1,1,2)-DATE(0,9,10)-WEEKDAY(A2))-1,9,10)+8)/7)

The two 9s represent September, the two 10s the 10th of that month, change for different rules….

Using that set up the next year with 53 weeks will be 2011/12

Based on the same assumption (i.e. financial year starts first Sun on or after 10th Sept) you can get period numbers with this formula.

=MIN(13,CEILING((INT((A2-WEEKDAY(A2)-DATE(YEAR(A2+DATE(1,1,2)-DATE(0,9,10)-WEEKDAY(A2))-1,9,10)+8)/7))/4,1))

If year has 53 weeks then week 53 will be in period 13

 

[mplees]

Hello Barry,

Many thanks for your most welcome assistance! Your formulae work perfectly, and once I get a response from our accountant, I should be able to adjust them to tally exactly with the start date of the financial year.

thanks again,

Mark

 

[mplees]

Hi again Barry,

Just had a reply back from our accountant, and he doesn't know what the rule is! However, he did supply me with the start dates for the previous 3 financial years:

0708 started Sun 16/09/07
0607 started Sun 17/09/06
0506 started Sun 18/09/05

....which I thought meant that it was always the 3rd Sunday of the month, but in the next financial year (0809), the start date is Sept 14th, which is the 2nd Sunday of the month! Not quite sure now where this leaves me - any thoughts as to how the start date might have been calculated?

Regards,

Mark

 

[asad]

Hello Mark,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I assume that the financial year can’t always have 52 weeks otherwise, eventually, your year start will be in March! What’s the rule for when the financial year starts each year? Assuming it always starts on the first Sunday on or after 10<SUP>th</SUP> September, for instance, use this formula to determine the week number, where date is in A2<o></o>
<o> </o>
=INT((A2-WEEKDAY(A2)-DATE(YEAR(A2+DATE(1,1,2)-DATE(0,9,10)-WEEKDAY(A2))-1,9,10)+8)/7)<o></o>
<o> </o>
The two 9s represent September, the two 10s the 10<SUP>th</SUP> of that month, change for different rules….<o></o>
<o> </o>
Using that set up the next year with 53 weeks will be 2011/12<o></o>
<o> </o>
Based on the same assumption (i.e. financial year starts first Sun on or after 10<SUP>th</SUP> Sept) you can get period numbers with this formula.<o></o>
<o> </o>
=MIN(13,CEILING((INT((A2-WEEKDAY(A2)-DATE(YEAR(A2+DATE(1,1,2)-DATE(0,9,10)-WEEKDAY(A2))-1,9,10)+8)/7))/4,1))<o></o>
<o> </o>
If year has 53 weeks then week 53 will be in period 13<o></o>

Hello Barry,
I would like to use your formula. But I have a problem with that. Can you please help me on this?
The problem I have is that our financial year is from 01st July to 30th June every year and the week is from Sunday to Saturday. What we have is that if the last week in June is stretching over to July, then that week should be counted as week number 1 for next year. e.g. this year the week starting 26/06/2016 should be week #1 for FY 2016-17 not week #53 for FY 2015-16.
Is it possible to do?

Thanks in advance for your help Barry.
Asad

 

[asad]

Here is the answer for whoever wants to use it.

=INT((B1+(7-WEEKDAY(B1))-(DATE(YEAR(B1+(7-WEEKDAY(B1)))-(MONTH(B1+(7-WEEKDAY(B1)))<7),7,1)+(7-WEEKDAY(DATE(YEAR(B1+(7-WEEKDAY(B1)))-(MONTH(B1+(7-WEEKDAY(B1)))<7),7,1)))-7))/7)