Excel Formula: Get Fiscal Year From Date
Usually, the fiscal month does not start from January for most companies and the fiscal year may stays in 2 nature years, says from 7/1/2019 to 6/30/2020. In this tutorial, it provides a formula to quickly find the fiscal year from a date in Excel.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
| YEAR(date)+(MONTH(date)>=start_month) |
Syntaxt and Arguments
| Date: the date that is used to find its fiscal year. |
| Start_month: the month that starts next fiscal year. |
Return Value
It returns a 4-digit numeric value.
How this formula works
To find the fiscal years from the dates in the range B3:B5, and starting fiscal months are in cells C3:C5, please use below formula:
| =YEAR(B3)+(MONTH(B3)>=C3) |
Press Enter key to get the first result, then drag auto fill handle down to cell D5.
Tips: If the formula results display as dates, says 7/11/1905, you need to format them as general numbers: keep the formula results selected, and then select General from the Number Format drop-down list on the Home tab.
Explanation
MONTH function: returns the month of date as number 1-12.
Using the formula MONTH(B3)>=C3 to compare if the month of the date is greater than the fiscal starting month, if yes, returns to FALSE which seen as 1 in next calculation step, if no, returns to FALSE which seen as zero.
YEAR function: returns the year of date in 4-digit serial number format.
=YEAR(B3)+(MONTH(B3)>=C3)
=YEAR(B3)+(5>=7)
=2019+0
=2019
Relative Formulas
- Count fiscal quarter from date
Generally, the fiscal quarter starts in a month other than January for most companies. In this case, how can you get the fiscal quarter based on the given date in Excel? - Count days until expiration date
To count the days between today and each expiration date in Excel by formula - Display current date or time
This tutorial provides formulas to show the current date or time in Excel. - Create weekly date range
In this tutorial, it provides a formula to quickly get the weekly date range in Excel.
Relative Functions
- Excel WEEKNUM Function
Returns the week number of the given date in a year - Excel YEAR Function
Returns the year of date in 4-digit serial number format - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel NOW Function
Get the current time and date
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.