Microsoft Excel - Help with fiscal week calculation formula.

I am trying to calculate the Fiscal Week, using the formula above.

In our organization, the Fiscal Week starts on a Wednesday & also ends on a Wednesday.

It works fine except for the Leap Year i.e. 2016 especially for 31st Aug 2016 which is also a Wednesday causing an incorrect computation.

Formula that I have used for Fiscal Week calculation is as given below :

BLANKVALUE(TEXT(YEAR(Close_Date__c)),Fiscal_Year__c) & " " &
IF(AND(MONTH(Close_Date__c)=1,DAY(Close_Date__c)=1), "01",
IF(Fiscal_Week__c = 13, "03",
IF(Fiscal_Week__c = 26, "06",
IF(Fiscal_Week__c = 39, "09",
IF(Fiscal_Week__c >= 52, "12",
IF(
MONTH((7-MOD(Close_Date__c - DATE(1900, 1, 4), 7)) + Close_Date__c) < 10, "0" & TEXT(MONTH((7-MOD(Close_Date__c - DATE(1900, 1, 4), 7)) + Close_Date__c)), TEXT(MONTH((7-MOD(Close_Date__c - DATE(1900, 1, 4), 7)) + Close_Date__c))
))))))

Hello Charvi,

I wonder whether you could use the WEEKNUM function with an adjustment to the date to allow for the Wednesday factor.

Something like = WEEKNUM(Your_Date - 3, 1).

I tried to use the formula that you printed to crosscheck answers but kept getting an error trying to input it so maybe something has gone wrong in the transcription and I couldn't take the time to try to debug it at the moment.

Hope this helps.

Harry