How to calculate the fiscal month (period) number from a date in Excel
Formula (Excel and Google Sheets)
=MOD(MONTH(A2)-$E$1,12)+1Count the months since the fiscal year started: =MOD(MONTH(A2)-$E$1,12)+1, with the start month in E1.
- Result on the example
- one result per row in
B2, filled down toB7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.
How it works
MONTH(A2)-$E$1 is how many months the date is past the fiscal year start, negative for months before July. MOD(…,12) wraps those into 0 to 11, and +1 turns them into periods 1 to 12: July is period 1 and June is period 12.
=MOD(MONTH(A2)-$E$1,12)+1In Google Sheets
The same formula works in Google Sheets: =MOD(MONTH(A2)-$E$1,12)+1. We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| ="P"&TEXT(MOD(MONTH(A2)-$E$1,12)+1,"00") | P01, P03, P06…Different result | A period label like P07. Text label. |
| =MONTH(EDATE(A2,13-$E$1)) | 1, 3, 6… | Shifts the date so the fiscal year starts in January, then takes the month. |
Common mistakes
- Without MOD, January to June come out as negative periods.
- Retailers on 4-4-5 calendars have periods that don't follow calendar months; this formula doesn't apply to them.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate the fiscal month (period) number from a date
B2, return the fiscal period (1 to 12) of the date for a fiscal year that starts in the month in E1. Fill down to B7.Questions
How do I get the fiscal quarter from the period?
=ROUNDUP(B2/3,0).
How do I get the fiscal year?
=YEAR(A2)+(MONTH(A2)>=$E$1), for a fiscal year named by the year it ends in.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.