Calculate Financial Week No

Hi All,
having problems calculating the Financial week no for a row of cells for a new.

Column A = Date. (e.g.1 Apr 2013)
Column B = Financial Week ( If 1 Apr = wk 1)


What I am using below here is based on calendar week. But I want to convert this to Financial week.

=INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7)


My financial Year starts on 1 Apr of ever year.
Starts on Mon and Ends on Sun.
It is based on Calendar Style 4-4-5.

Using either function or Macro is fine.

Any Help is appreciated!

Regards,
Nironto

Attached Sheet for Easier understanding.

Now Column A = Dates, B = Calendar Week, Now I put in Financial Week in Column C.
Instead of keying in Manually. I need a method / function to compute the Financial Weeks in Column C.


Regards,
Nironto

Thanks Fotis1991!
Few Issues.

Issue 1
Week 1 = Apr 1 to Apr 7.
Yours is at Apr 1 to Apr 6.

Issue 2
And when it comes to 1 Apr of every year, (In this case 2014)
It should restart as week 1.
You may refer to the picture snapshot of how the Financial week based on 4-4-5 for 2013 for how it looks like.

Regards,
Nironto

Thanks for trying. I know this is a guru level challenge.

Hi Fotis1991,
Thanks. My friends and I been trying whole day and we concluded it really tested our wits and is beyond us or is even doable??.
I had solutions provided by you before and have been very impressed by your expertise. No doubt, you are a guru and we also wanted to be like you.

I had left office and I'll let you know the first thing in the morning. Many Thanks!

Regards,
Nironto

Hi daddylonglegs,
Yes. There is week 53.

I have attached the Financial Week Calendar (See the tab)--Refer to updated Excel Sheet
The Financial week always begins on Monday and ends Sun.
It resets on 1 apr every year as week 1.
( though it may not always fall on Monday. which means e.g. week 1 of yr 2014 may only have 6 days).

Hi Fotis1991,
Really appreciate your help! You are getting very close. (If you refer to the Grey columns which is the actual result and compare the formula values.)
The formula is correct till it reaches the new year of 2014.

This calendar thingy is beyond my level and is killing me. Thanks to all for helping.

Regards,
Nironto

Thanks daddylonglegs and Fotis1991!
You guys are awesome! A living legend!

I thought this is mission impossible but you guys made my day!
I had learn something along the way too!
Yeah, can you explain more on your logic ?

I'll update this thread as solved.

Regards,
Nironto