Calculating the week within Fiscal year in calculated field

Question

I have been asked to make a list that contains a calculated column containing the week number within a fiscal year starting on 6th April 2015 (Monday) So for example, 6th-12th April would be week 1, 13th-19th April week 2 and so on.

Current columns are Title, Date, Expected Fin week (what it should be) and calculated fin week (new).

So I would need the calculated fin week to be based upon the Date column (but I presume you knew this already)

I'm a very new, basic user of SharePoint so any help on this at all would be fantastic, thanks very much.

Bash.

Answer 1

There is no direct function which can give you this value. You can calculate this based on

1. Find number of weeks between January and April
2. Find number of weeks from your Date Field and January
3. Now Add Result of #1 wit Result of #2

You can find week from date using formula

=INT(([DateField]-DATE(YEAR([DateField]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([DateField]),1,1)),"d")))/7)

Answer 2

I solved this in a two-step fashion.

1. Formula for calculating elapsed day of fiscal year.
2. Formula for calculating fiscal week based off of elapsed days of fiscal year

This formula calculates the elapsed day of the fiscal year - for my purposes as of April 1. You'll need to tinker with the month value as fit for your purposes.

=date-DATE(YEAR(date)+(MONTH(date)<4)*-1,4,1)+1

The second formula I used took that elapsed day value and calculated it into a work week.

=ROUNDDOWN(((day_of_fiscal_year-1)/7),0)+1

The -1 in the second formula is to offset the elapsed day result and keep the week numbers aligned so that the 7th, 14th, 21st day results get pulled into the first, second and third weeks respectively. The +1 at the end prevents you from having a Week 0.