I need to group sales records in a pivot table by our company’s fiscal year, fiscal quarter and fiscal month. Attached is a spreadsheet containing dates that need to be grouped. Ideally, the solution should be able to recognize future fiscal dates, too.
Our fiscal months:
- START: the first day (Saturday) after the last Friday each month (note: this Saturday can belong to the current or the next month)
- END: the last Friday each month
Example: May 26 2012 – June 29 2012
Our fiscal years:
- START: on the first day (Saturday) after the last Friday in April (note: Saturday can belong to May)
- END: on the last Friday in April
- Examples:
o April 30 2011 – April 27 2012
o April 28 2012 – April 26 2013
Our fiscal Quarters:
- Examples:
o Quarter1: April 30 2011 – July 29 2011
o Quarter2: July 30 2011 – October 28 2011
o Quarter3: October 29 2011 – January 27 2012
o Quarter4: January 28 2012 – Apr 27 2012
Thank you.
Bookmarks