Excel/Week Number Formula
I have now run into a problem and need your help/advise. Hope you can help, its an Excel Formula which I am just not able to get it right or figure correctly.
I have a Excel Sheet which creates a 12 Month Yearly Calendar with week numbers.
The 4 parameters that the user enters are
1. the desired Year
2. The month to start from
3. Start Day for the calendar week (Monday or Sunday)
4. US Style or ISO (European Style Calendar)
The calendar works fine upto this point.
What I need is additional Fiscal Year Week Numbers
My fiscal year starts from 1st Monday of April regardless of the date it starts from
Please see attached image
The Formula I have used is
This works fine for the year 2014 or any other year where the 1st monday is falling in the 2nd week of april. But thats not what i want.
I need it to calculate the week number as 1 from 1st monday in April for any year
The formula I have used to calculate normal week numbers is as follows
If need be I can email the Excel File itself as I find it hard to explain any more than this
I do not need vba code ... only excel formula to calculate fiscal week numbers starting from 1st monday of April for any given year.
Thanks in advance..
ANSWER: This link
seems to give the answer you want
---------- FOLLOW-UP ----------
Thanks for the reply. I had already reffered to that link and it had not helped me that's the reason why I had asked this Question here.
OK let me try and simplify this so it can be answered.
I have dates stored from C21:I21 and C22:I22 and so on upto C26:I26 as dates appearing in April for any given year defined in A4 starting from Monday to Sunday as in ISO standard
I need a formula in J21:J26 to display Fiscal weeknumber starting from 1st Monday in April
Therefore in 2013 Monday 01-Apr-2013 will be week 1 and so on but in 2014, Monday 07-Apr-2013 will be week 1 and so on
Thanks in advance again.
The bottom of the thread looked like it would do it, but I'll happily accept I misread the problem - would suggest storing the actual start date of the fiscal year in a cell somewhere - this formula will give you the first monday of april for the current fiscal year based on the now function - if you want that to calculate the fiscal year for a given date, replace now() with a reference to that date. Once you have the date, deducting it from the value you want to display the fiscal week number and dividing by 52 (and turning that into an integer) should do it for you
Sorry for the delay in replying - we have been affected by the Noro virus in our household