Date field in Pivot table

[krishnaraj]

I have a unique case where I have 500 projects with start dates varying from 1/1/2013 to 12/31/2028. I would like to extract data based on fiscal year on the pivot table, where the fiscal year would be from Apr - Mar. Currently it is not grouped based on the criteria. Any help would be greatly appreciated.

 

[bobhc]

Good day krishnaraj

Right click in the data field, choose group from the menu and group the dates from there. Or even easier is to turn your raw data that you used for the pivot table into a table and filter the dates from there, also if you have your raw data as a table any update of data will refresh in the pivot.

 

[Faseeh]

Hi Krishnaraj,

Besides what bobhc said, you can:

1. Add a helper column to your data.
2. Label it "Fiscal year".
3. Use YEAR() to extract year.
4. Add it to your pivot table..

..and continue with your pivot table.

 

[krishnaraj]

Thanks Bob and Faseeh, the catch I had was the fiscal year not starting in Jan. Currently if I have a date which reads 2/1/2015, this should go into FY 2014-2015 and should read as 2014 and not 2015.