Group on quarters

[Karanbir Soin]

Hi,
My Name is Karanbir, I am from India. In our financial system first quarter begins on First of April, and last quarter ends on last day of March.
How can this be changed on MS Excel. I am using Office 2010.
In my pivot table when I use group by quarters the first quarter starts in Jan and last quarter is in Dec.
I am using Pivot table for first time.
Regards.

 

[oldchippy]

Hi Karanbir Soin,

Welcome to the forum, please take time out to read the rules of the forum if you haven't already, here is the link here

http://chandoo.org/forum/forums/new-users-please-start-here.14/

With regard to your question, maybe this link will help

[url]http://www.contextures.com/xlPivot07.html#Dates[/URL]

 

[Somendra Misra]

Hi Karanbir Soin,

Please find the attached file. Is that what you require?

Just advise if any issue.

Regards!

 

[Karanbir Soin]

Thanks Somendra,
I am attaching my file. It has the dates. when I group on qtrs. it gives the result in sheet2. Pivot is based on data in sheet 2.
I want to re align the qtrs. as per Indian system.

Regards.

 

[Somendra Misra]

@Karanbir Soin

Please have a look of attached file.

Regards!

 

[Karanbir Soin]

@Karanbir Soin

Please have a look of attached file.

Regards!

Wonderful Somendra. You made my day.
Thanks

 

[Debraj]

Just a lil bit longer Formula.. but more flexible..

="Quarter " & MOD(CEILING(22+MONTH(A1)-3-1,3)/3,4)+1

In case if you need March as End Quarter Month.. Use 3, and if you need December as End Quarter Month.. Use 12..

http://www.exceltip.com/excel-date-time-formulas/calculating-the-quarter-number.html

 

[Somendra Misra]

@Karanbir Soin
Glad you solve it & thanks for your kind words.
Just try Debraj Formula. Thanks Debraj.

Regards,