Fiscal Month and Period Number From a Date in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To get the fiscal month from a date, move the date back by the months before your fiscal year starts, then take the month. For a year starting in July, use =MONTH(EDATE(A2,-6)): March returns 9, July returns 1. For any start month in E1, use =MOD(MONTH(A2)-$E$1,12)+1. Both work in every Excel version.
Key takeaways
- Fiscal month = the month number counted from the first month of your fiscal year.
- For a fiscal year that starts in month S, use =MONTH(EDATE(A2,1-S)) or =MOD(MONTH(A2)-S,12)+1.
- A July start uses -6: =MONTH(EDATE(A2,-6)). An April start uses -3, an October start uses -9.
- Build period labels like P09 with ="P"&TEXT(fiscal_month,"00").
- 4-4-5 and 52/53-week calendars don't follow calendar months, so use a lookup table of period start dates.
On this page+
The fiscal month in Excel is the month's position in your company's financial year. If the year starts in July, July is fiscal month 1 and March is fiscal month 9. You get it by shifting the date back to the start of the fiscal year and taking the month: =MONTH(EDATE(A2,-6)) for a July start. This guide is for finance, sales and operations staff who report by period, and it covers any start month, P01 labels and fiscal quarters.
What is a fiscal month?#
A fiscal month is a month numbered from the start of the fiscal year instead of from January. A fiscal year is the 12-month period a company uses for its accounts. Many start on 1 January, but plenty don't: July starts are common in Australia, the US federal government starts in October, and April starts are common in the UK and India.
With a July start, the months line up like this:
| Calendar month | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Apr | May | Jun |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Fiscal month | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
Reports often call these periods P01 to P12. The calendar month name stays the same; only its number changes.
How to get the fiscal month with MONTH and EDATE#
Move the date back by the number of months before the fiscal year starts, then take the month. EDATE moves a date by whole months, and MONTH returns the month number from 1 to 12:
=MONTH(EDATE(A2,-6))For a July start, six months (January to June) come before the fiscal year, so the shift is -6. Shifting 15 March 2025 back six months gives 15 September 2024, and MONTH returns 9.
| A | B | |
|---|---|---|
| 1 | Date | Fiscal month |
| 2 | 1 Jul 2024 | 1 |
| 3 | 10 Oct 2024 | 4 |
| 4 | 15 Mar 2025 | 9 |
| 5 | 30 Jun 2025 | 12 |
The shift is always 1 minus the start month: -3 for April, -6 for July, -9 for October. A January start needs no shift at all. The EDATE function guide explains the function, and the article on EDATE and EOMONTH shows other ways to move dates by months. EDATE keeps month-end dates safe: 31 December shifted back six months becomes 30 June, so the month is still right.
Fiscal month formula for any start month#
Put the start month in a cell and use MOD, so one formula works for any company. If E1 holds the first month of the fiscal year (7 for July):
=MOD(MONTH(A2)-$E$1,12)+1Here is how it works for 15 March with a July start:
MONTH(A2)returns 3.3 - 7gives -4.MOD(-4,12)wraps it into the range 0 to 11 and returns 8.+1turns it into 9.
Change E1 to 4 and every row recalculates for an April year. The $ signs keep E1 fixed as you fill down (Windows: Ctrl+D; Mac: ⌘D). You can also write it with EDATE: =MONTH(EDATE(A2,1-$E$1)) gives the same answers.
How to build period labels like P09 or FY2025 P09#
Use TEXT to pad the fiscal month to two digits, then join it to a letter:
="P"&TEXT(MONTH(EDATE(A2,-6)),"00")For 15 March this returns P09. Two digits matter: P09 sorts before P10 as text, while P9 would sort after P10.
Add the fiscal year when your reports cover more than one year. For a July start named by the year it ends in, the fiscal year is YEAR(EDATE(A2,6)):
="FY"&YEAR(EDATE(A2,6))&" P"&TEXT(MONTH(EDATE(A2,-6)),"00")For 15 March 2025 the result is FY2025 P09. Some companies name the fiscal year by the year it starts in instead. The fiscal year from a date guide covers both naming styles. If you also want the month name, =TEXT(A2,"mmm") returns "Mar"; see how to get the month name from a date.
How to get the fiscal quarter from the fiscal month#
Divide the fiscal month by 3 and round up:
=ROUNDUP(MONTH(EDATE(A2,-6))/3,0)Fiscal month 9 gives 9 / 3 = 3, so 15 March is in fiscal Q3. Fiscal months 1 to 3 are Q1, 4 to 6 are Q2, and so on. The guide to getting the quarter from a date shows the calendar version and more label styles.
How to total sales by fiscal month#
Add a helper column with the fiscal month, then total with SUMIF. If the dates are in A2:A200, the amounts in C2:C200 and the fiscal months in B2:B200:
=SUMIF($B$2:$B$200,F2,$C$2:$C$200)Put 1 to 12 in F2:F13 and fill down for a full fiscal-year summary in period order. A helper column is easier to check than a long array formula, and a pivot table can use it as a row field too. For calendar-month totals, see how to sum by month.
What about 4-4-5 and 52/53-week calendars?#
Some retailers and manufacturers use a 4-4-5 calendar (periods of 4, 4 and 5 weeks) or a 52/53-week year. Their periods start on fixed weekdays, not on the 1st, so no MONTH formula can find them.
Instead, list the start date of every period in a table, oldest first, with the period number next to it. Then look each date up with an approximate match. If the start dates are in H2:H14 and the period numbers in I2:I14:
=XLOOKUP(A2,$H$2:$H$14,$I$2:$I$14,,-1)The -1 means "exact match or the next smaller item", so any date returns the period it falls in. XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, =VLOOKUP(A2,$H$2:$I$14,2,TRUE) does the same when the start dates are sorted.
Practise it#
Fiscal months are date arithmetic plus one EDATE. Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet: you move dates by months and the sheet checks each answer instantly. If dates still feel slippery, start with the date basics lesson. Then try the exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The company's fiscal year starts on 1 July. Click B4 and use MONTH and EDATE to find the fiscal month of the date in A4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fiscal month | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 |
Tip: A July start means six months come before it. Move the date back 6 months with EDATE(A4,-6), then wrap that in MONTH.
Practise this in a lesson
Frequently asked questions
How do I calculate the fiscal month in Excel?
Shift the date back by the months before the fiscal year starts, then use MONTH. For a July start, =MONTH(EDATE(A2,-6)) turns 15 March into fiscal month 9.
What is the formula for fiscal period in Excel?
The fiscal period is usually the fiscal month as a number from 1 to 12. Use =MOD(MONTH(A2)-S,12)+1, where S is the first month of the fiscal year, and add TEXT for labels like P09.
How do I get the fiscal month for an April fiscal year?
Use =MONTH(EDATE(A2,-3)). April becomes 1, December becomes 9 and March becomes 12.
How do I show the fiscal month name instead of a number?
The month name doesn't change, only its position. Use =TEXT(A2,"mmm") for the name, and the fiscal month formula for the number, then join them if you need a label like 09 Mar.
Does the fiscal month formula work with 4-4-5 calendars?
No. In a 4-4-5 or 52/53-week calendar, periods start on set dates rather than on the 1st. List each period's start date in a table and look the date up with an approximate match.