Excel Fiscal Year in a PivotTable | Fiscal Year different from Calendar Year in Excel

For many companies, the fiscal year is different than the calendar year. In today's video, I create a PivotTable in Microsoft Excel and show you how to calculate the fiscal year in your data and use it in a PivotTable.

We've created many PivotTables in previous videos, and today is no different. We create a simple date and sales PivotTable and show quickly how Excel takes the dates and organizes them by calendar year.

We then use some simple formulas to take the dates and display the fiscal year for those dates. We have to write nested Excel functions, using the YEAR() and MONTH() functions to return the year and month, respectively. We then change the function to add a condition that returns true or false, depending on the month we choose as the start month for our custom fiscal year.

The function we use takes the YEAR() formula, then the MONTH(), and adds a condition to it to decide whether that month is within the fiscal year of our choice by comparing the month number.

Chapters:
00:00 Introduction
00:43 Example with calendar year
01:25 Example with different month
02:25 Function for fiscal year from month
03:57 PivotTable with adjusted fiscal year
04:20 Explanation of function used

I have a lot of Excel articles on my blog:

More Excel Videos in my dedicated Excel Training Playlist:

Chris Menard's website

#excel #pivottables #excelfiscalyear

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[danielornelasahumada]

Great video! The formula below, from Chat GPT, gave me better results. The one you provided assigned January 2021 to fiscal year 2021 for me, but our fiscal year starts in March. So, the formula below did the trick for me:

=IF(MONTH(A2)>=3, YEAR(A2), YEAR(A2)-1)

[shawnbatty]

OMG. I LOVE YOU. I manually input thousands of rows FY23. :-( You saved me hours of work. Thank you so much.

[viviromo]

YOU JUST SAVED ME A TON OF TIME! You’re the best!

[ROOKIV]

Fantastic! Saved me loads of tedious work.TY!

[thehunterseeker]

Chris, you Sir are an absolute legend, very clearly and concisely explained. Now, for me to make it work.

[rotethongvanit]

Without you, I really don't know how. Thanks so much

[cyberl]

first time I needed this and you showed me the answer in 5mins. Thank you for your video creating efforts!

[jsl]

Very well explained!
Thanks for sharing Chris.

[hblakey]

This is amazing! I've done this in such hard ways before!

[johnborg]

Thanks Chris. That was very informative and helpful. Thanks again. : ) : )

[ganny]

This is great, thank you. exactly what I was looking for

[chickenbroncitis]

Great helpful video. Im not too great with Excel but you explained it very well to me. Thanks.

[brenomachado]

I think the easiest way it to use the end of month with an offset. Want to get the financial year beginning in July. Use Eomonth(date, 6)

[dltyler]

This a keeper. I work in academic years (Aug - Jul ).

[muzammilkadri]

Hi Chris, Nicely explain. I want to take range of FY like 201-2020 and so on. Could you pls help me with the formula.

[KristinaSuhGCR]

thanks, been trying to do this for awhile. If I want to add the month, but have them list in fiscal year order, how do i do that in a pivot table. I would also like to display all months, even if they are blank. thanks in advance

[acehays]

Trying to do this as a calculated field in a table from SQL in powerpivot pro and while it does not throw an error it also does not work, so for instance if I do month >= 2 it still shows Jan in the current year too, any thoughts?

[ubaidillahmuhammad]

good job. Put excel practiced files in the description.

[bzflowerbee]

Do you know how to create a 445 calendar table?

[danielboileau]

Hi Chris, me again. Works well but how can you fix the month issue? How can you get monthly figures starting with the first month of your fiscal year lets say April. Excel keeps on putting January as the first month even if the first day of my fiscal year is April 1st. Thanks in advance