Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate the fiscal year from a date in Excel and Google Sheets
Topic:Date arithmeticExcel & Google Sheets
=IF(MONTH(B2)>=7, YEAR(B2)+1, YEAR(B2))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Date | Fiscal Year |
| 2023-06-30 | |
| 2023-07-01 | |
| 2024-01-01 | |
| 2024-12-31 |
=IF(MONTH(B2)>=7, YEAR(B2)+1, YEAR(B2))→1900
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your date in cell B2 of your spreadsheet.
- 2In cell C2, enter the formula provided for Excel or Google Sheets.
- 3Press Enter to see the fiscal year calculated based on the date.
Tips
- Ensure your date format is consistent across your spreadsheet.
- Adjust the month in the formula if your fiscal year starts on a different month.
- This formula assumes the fiscal year starts in July and ends in June.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if my fiscal year starts in January?
Change the formula to =YEAR(B2) since the fiscal year aligns with the calendar year.
How can I handle dates in different formats?
Convert all dates to a standard format using Excel's DATEVALUE or Google Sheets' DATE functions before applying the fiscal year formula.
Can this formula be used for multiple dates at once?
Yes, copy the formula down from C2 to apply it to other dates in column B.
More on Date arithmetic
See all →Reference
TODAY / DATEDIF
Work with the current date and measure the gap between two dates.
How-toHow to calculate age from a date of birth
Turn a birth date into an age in years with DATEDIF and TODAY. Works the same in Excel and Google Sheets.
ReferenceDATEDIF
Calculates the difference between two dates in years, months, or days using a unit code string.
How-toHow to calculate days between two dates
Subtract one date from another to get the number of days, or count working days with NETWORKDAYS. Works in both apps.
How-toHow to add months to a date
Shift a date forward or back by a number of months with EDATE — it handles month lengths correctly. Works in both apps.
How-toHow to flag overdue dates
Mark rows whose due date has passed by comparing it to TODAY() with IF. Works the same in Excel and Google Sheets.