Fiscal Year Calculated Columns

pvanolinda

I am assuming that you want to find the the weeknumber with starting of fiscal year from 19/06/2016.

I see that you have a calendaryear column, date column in your calendar table.

Try the following.

1. First convert the Date column into date format type DD/MM/YYYY

2. Create a FiscalYear column using the formula

     =If((Month([Date]) >= 6 && Format([Date],"MMDD") >= "0619") , [Calendar Year],[Calendar Year] -1 )

3. Create column StartofYear which sets the starting of the year for each row in the date table as 

    ="19/06/"&Format([FiscalYear],"####")

    This will create the same value for each row and changes for every fiscal year setting it as 19/06/2016,19/06/2017, etc for each

    row depending on the fiscal year value.

4. Create  a column called FiscalYearWeekNumber based on fiscal year starting from "19/06/2016".

    =CEILING((WEEKDAY([StartofYear])+[Date]-[StartofYear])/7,1)

This just relies on the difference between the calendar date and day 1 of the FY, and the fact that weeks contain 7 days. The WEEKDAY function factors in what day of the week the FY starts on, and the date difference and CEILING function handle fractional week differences due to the day of the week the date falls on.

With this done you will find that for each year starting from 19/06 weeks are numbered based on Sun to Saturday weeks.

Try it out 

If this works for you please accept it as a solution and also give kudos.

Cheers

CheenuSing

So really, it's just an offset. So you can define a custom Fiscal Week Number column where the condition is as follows.

if [Week Number] > 25 then [Week Number] - 25 else [Week Number] + 27

This should calculate your week number correctly, though the exact date will change from year to year.

For the pay period, you could use the IsOdd or IsEven condition get every other week.

Hope this helps.

Treb Gatte, Business Solutions MVP | @tgatte | Blog | CIO Magazine Blog