A lot of times you might want to calculate Financial Year (also known as Fiscal Year) and Assessment Year based on the date. For e.g., you might track expenses by date and want to check how much you spent in a financial year. I use an excel sheet to keep a track of my expense on Facebook marketing:
Now some countries use the calendar year as the financial year and for them, it is pretty straightforward. However, many countries, including India, have different financial year and calendar year. I will show how you can convert the date to the fiscal year. I have used India’s fiscal year for the formula, but you can easily change it to reflect the standard followed in your country. Note that this works on Google Sheets as well.
Assuming that the date is in cell A2, type the formula below in the cell where you want fiscal year to appear:
How The Formula Works
- MONTH(A2) extracts the month from the date.
- We check if the month is greater than 3 (This is because in India financial year ends in March, which is the 3rd month. Change this as per your country.)
- If the above condition is true, then the formula concatenates
year of the dateand
year +1. e.g., if the date is 2nd Apr, 2018 then as the above condition is fulfilled, the formula returns 2018-2019 as the financial year (year of date & year of date +1)
- If the above condition is false, then the formula concatenates
year of the date - 1and
year. e.g., if the date is 2nd Jan, 2018 then the formula returns 2017-2018 as the financial year (year of date -1 & year of date)
The same logic can be used to find assessment year, just use the fact that assessment year is one greater than the financial year.
Hope this tutorial helps you to calculate financial year and assessment year. In case you face any issue drop a comment.