SOLVED

how do i change the default fiscal year in excel

Brass Contributor

Microsoft automatically identifies the quarters in a fiscal year that starts in January.

 

My fiscal year starts in November; how can I change the automatic fiscal year to fit this?

6 Replies

@LilYawney 

You can change the default fiscal year in Excel by following these steps:

 

Choose File > Options, and then select Schedule.

In the Fiscal year starts in box, select the month with which you want the fiscal year to start.

If you want to label the fiscal year by using the calendar year in which that fiscal year begins (rather than the calendar year in which it ends), select the Use starting year for FY numbering check box.

 

I hope this helps!

best response confirmed by LilYawney (Brass Contributor)
Solution

@NikolinoDE 

Niko, that link is for Microsoft Project, not for Excel. As far as I know, Excel does not have a built-in concept of fiscal year.

@NikolinoDE 

I'm probably a bit upset today, I have so much around me today...

Hans Vogelaar  I apologize for my mistake :).
 
@LilYawney It seems that there is no built-in way to change the default fiscal year in Excel.

The only thing I could find on the internet, a workaround might be to create a helper column and fill the month numbers in there.

For example, if you want July to be 1 and June to be 12, you could use a formula to populate the helper column with the appropriate month numbers.

 

Thank you both for your understanding and patience :).

 

I have the same issue, is there really no way to change to the fiscal start month?? The quarterly pivot table is off.

@Scott_M330 

Here is a possible solution. The exact formula depends on your definition of fiscal year; in this example it is

=YEAR(A2)+(MONTH(A2)>=$F$1)

 

HansVogelaar_0-1691053942025.png

@Scott_M330 If you are using Excel 365/Excel for the web, you can use the LAMBDA function (a better description is in this AbleBits article) to create a formula that you name and store in the Name Manager.  Then you can use that name as a custom function throughout your workbook.  This has the advantage that any future change to the custom formula can be done in only one place.

 

For a fiscal year starting in July, the formula could be:

=LAMBDA(some_date, IF( MONTH(some_date) < 7, YEAR(some_date), YEAR(some_date)+1 ) )

 …which could be defined as, e.g., MyFiscalYear:

$Name Manager.png

An example of its use:

MyFiscalYear.jpg

 

1 best response

Accepted Solutions
best response confirmed by LilYawney (Brass Contributor)
Solution

@NikolinoDE 

Niko, that link is for Microsoft Project, not for Excel. As far as I know, Excel does not have a built-in concept of fiscal year.

View solution in original post