You are here:

Excel/Week Number Formula

Advertisement


Question
Fiscal Year Week Number
Fiscal Year Week Numbe  
QUESTION: Hi

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
=IF(AND(C22="",I22=""),"",IF(weeknumopt="US",WEEKNUM(MAX(C22:I22),$A$6),MOD(ROUND((MAX(C22:I22)+121)/7,0),52)+1))

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(AND(C12="",I12=""),"",IF(weeknumopt="US",WEEKNUM(MAX(C12:I12),$A$6),1+INT((MAX(C12:I12)-DATE(YEAR(MAX(C12:I12)+4-WEEKDAY(MAX(C12:I12)+6)),1,5)+WEEKDAY(DATE(YEAR(MAX(C12:I12)+4-WEEKDAY(MAX(C12:I12)+6)),1,3)))/7)))

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

Dhaval Kapadia

ANSWER: This link
http://www.mrexcel.com/forum/excel-questions/346959-calculating-week-number-with
seems to give the answer you want

---------- FOLLOW-UP ----------

QUESTION: Hi
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

Please help.

Thanks in advance again.

Answer
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

=IF(8-WEEKDAY(IF(MONTH(NOW())>3,DATE(YEAR(NOW()),4,1),DATE(YEAR(NOW())-1,4,1)),2)=7,IF(MONTH(NOW())>3,DATE(YEAR(NOW()),4,1),DATE(YEAR(NOW())-1,4,1)),IF(MONTH(NOW())>3,DATE(YEAR(NOW()),4,1),DATE(YEAR(NOW())-1,4,1))+8-WEEKDAY(IF(MONTH(NOW())>3,DATE(YEAR(NOW()),4,1),DATE(YEAR(NOW())-1,4,1)),2))

Sorry for the delay in replying - we have been affected by the Noro virus in our household
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.