Converting Date to Fiscal Calendar Date in Excel

This is a follow up post to Fiscal Calendar Calculations Cheatsheet for Tableau.

Excel is a very important tool for data analysis and calculations. It's also an important data repository for Tableau. Some of the calculations can be made in Excel before the data is loaded into Tableau or other tools.

So here we are going to show the conversion from a regular date to a week-based financial calendar date, via Excel formula. We still use Apple's 5-4-4 Fiscal Calendar as an example.

The First Week of a Fiscal Year

It is the week that includes October 1st which is an Anchor Date. From the first week, we can determine the Fiscal Year.

Fiscal Year

Here is the formula for calculating Fiscal Year.

Note that B2-WEEKDAY(B2)+1 is the first day of the fiscal year. From this day on, the new fiscal year starts.

The First Day of the Fiscal Year

In the above Fiscal Year calculation, we used B2-WEEKDAY(B2)+1 to get the first day of the week that includes date B2. This is equivalent to DATETRUNC('week', B2) function in Tableau.

Leap Fiscal Year

A common fiscal year has 52 weeks and a leap fiscal year has 53 weeks. We will use this property to determine if a fiscal year is a leap year or not.

This formula allows us to get the total number of weeks between the start dates of two consecutive fiscal years.

The formula can be simplified a little: (E2-WEEKDAY(E2)-(D2-WEEKDAY(D2))/7

The logic test can be written as E2-WEEKDAY(E2)-(D2-WEEKDAY(D2)=371. If true, then it is a leap fiscal year.

Fiscal Week

The fiscal week of a common date is its distance to the start week of the fiscal year.

Fiscal Month/Period

In a common fiscal year, we will have 5-4-4 weeks for each of the 4 fiscal quarters. In a leap fiscal year, we will have 5-4-5 weeks in the first fiscal quarter. That is, the 3rd fiscal month/period will have 5 weeks in a leap fiscal year instead of 4.

Here is the formula for calculating fiscal month/period

Fiscal Quarter

It's easy to create the formula for getting fiscal quarters from fiscal months.

Demo File for Download

We have an Excel file for you to download (Click link and open it in Google spreadsheet. Go to File and click Download.) It has all the above formulas and all the fiscal years in the next 1000 years. Feel free to use it in your data analysis.

Conclusion

With the above formulas, we save the dependency on a database table for converting a common date to fiscal date. The above example is based on a 5-4-4 week-based fiscal calendar. It may not match exactly your fiscal calendar. Nonetheless it may give you some ideas for creating your conversion formulas.

Feel free to leave comments or contact me at @aleksoft at twitter.