FORMULA & SQL / DATES
Fiscal year from a date.
Tested, not guessed.
The Excel and Google Sheets formula and the SQL query, each run on the rows below. Open this with your own data ↗
The example rows
| Row | A: Region | B: Rep | C: Amount | D: Order Date | E: Status | F: Customer |
|---|---|---|---|---|---|---|
| 2 | North | Ana | 1,200 | 2026-03-01 | Paid | Jo Smith |
| 3 | South | Ben | 450 | 2026-03-15 | Open | Raj Patel |
| 4 | North | Cy | 300 | 2026-04-02 | Paid | Lee Wong |
| 5 | East | Ana | 980.50 | 2026-04-20 | Paid | Mia Ross |
| 6 | North | Ben | 75 | 2026-05-05 | Open | Jo Smith |
| 7 | West | Dee | 1,640 | 2026-05-18 | Paid | Sam Okoro |
The formula
Excel
=YEAR(D2)+(MONTH(D2)>=4)
The year of Order Date (D2) plus the month of Order Date (D2) is at least 4.
Named after the calendar year it ends in.
Run on the example rows:
| Row | Result |
|---|---|
| 2 | 2026 |
| 3 | 2026 |
| 4 | 2027 |
| 5 | 2027 |
| 6 | 2027 |
| 7 | 2027 |
The SQL
PostgreSQL
SELECT *, EXTRACT(YEAR FROM order_date) + CASE WHEN EXTRACT(MONTH FROM order_date) >= 4 THEN 1 ELSE 0 END AS fiscal_year FROM sales
Other databases
MySQL
SELECT *, YEAR(order_date) + CASE WHEN MONTH(order_date) >= 4 THEN 1 ELSE 0 END AS fiscal_year FROM sales
SQL Server
SELECT *, YEAR(order_date) + CASE WHEN MONTH(order_date) >= 4 THEN 1 ELSE 0 END AS fiscal_year FROM sales
SQLite
SELECT *, CAST(strftime('%Y', order_date) AS INTEGER) + CASE WHEN CAST(strftime('%m', order_date) AS INTEGER) >= 4 THEN 1 ELSE 0 END AS fiscal_year
FROM salesBigQuery
SELECT *, EXTRACT(YEAR FROM order_date) + CASE WHEN EXTRACT(MONTH FROM order_date) >= 4 THEN 1 ELSE 0 END AS fiscal_year FROM sales
Snowflake
SELECT *, YEAR(order_date) + CASE WHEN MONTH(order_date) >= 4 THEN 1 ELSE 0 END AS fiscal_year FROM sales
Your own data
Paste your rows into the free tool and it writes this for your columns, then runs it on your data before you copy it. Open the tool ↗