I have an Excel workbook with a table of sales data. I've appended a column titled Fiscal Year to the table to calculate fiscal year based on a value entered in a named cell, FiscalStartMo
It works great in the table. The column in the table correctly shows the proper value. Right now the FiscalStartMo is set to 1. The years display to 2017, as expected (see figure 1 below)
[TABLE="width: 500"]
<tbody>[TR]
[TD]fig 1) Fiscal Year in table[/TD]
[TD]fig 2) Fiscal Year in Pivot Table[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
Here's the problem: when I add the column to a pivot table, the available years go to 2018 (see fig 2 above), and data appear in Nov and Dec. How can this be? How do I fix this?
FYI, here is the formula used to calculate Fiscal Year (based on the Order Date in C2) =YEAR(DATE(YEAR(C2),MONTH(C2)+(FiscalStartMo)-1,1))
It works great in the table. The column in the table correctly shows the proper value. Right now the FiscalStartMo is set to 1. The years display to 2017, as expected (see figure 1 below)
[TABLE="width: 500"]
<tbody>[TR]
[TD]fig 1) Fiscal Year in table[/TD]
[TD]fig 2) Fiscal Year in Pivot Table[/TD]
[/TR]
[TR]
[TD]
[TD]
[/TR]
</tbody>[/TABLE]
Here's the problem: when I add the column to a pivot table, the available years go to 2018 (see fig 2 above), and data appear in Nov and Dec. How can this be? How do I fix this?
FYI, here is the formula used to calculate Fiscal Year (based on the Order Date in C2) =YEAR(DATE(YEAR(C2),MONTH(C2)+(FiscalStartMo)-1,1))
