Special Dates (Fiscal Month, Qtr and Year)

[bimmer5]

I am working on a typical sales query and I need to generate three new fields based on the Date field. For each Date record, I need to calculate its pertaining:
- fiscal accounting month
- fiscal accounting quarter and
- fiscal accounting year the date belongs to.

Fiscal Months:
Unlike typical fiscal months based on calendar months, the fiscal months start and end at odd times, for example
- START DAY: the first day (Saturday) after the last Friday of each month (note: this Saturday can belong to the current or the next month)
- END DAY: the last Friday of the following calendar month
Example1: for all dates between: Saturday, Apr 30 2011 – Friday, May 27 2011 ………….output: “Fiscal May-2011”
Example2: for all dates between: Saturday, May 28 2011 – Friday, Jun 24 2011 ………….output: “Fiscal Jun-2011”, etc…

Fiscal Quarters examples:
- for all dates between: April 30 2011 – July 29 2011…………………..output: “Fiscal Qtr1-2011”
- for all dates between: July 30 2011 – October 28 2011………………output: “Fiscal Qtr2-2011”
- for all dates between: October 29 2011 – January 27 2012………….output: “Fiscal Qtr3-2011”
- for all dates between: January 28 2012 – Apr 27 2012……………….output: “Fiscal Qtr4-2011”
- for all dates between: April 28 2012 – July 27 2012…………………..output: “Fiscal Qtr1-2012”, etc…

Fiscal Years examples:
- START: on the first day (Saturday) after the last Friday in April (note: the first Saturday can belong in May, too)
- END: on the last Friday in April next year
- April 30 2011 – April 27 2012………output: “Fiscal 2011-2012”
- April 28 2012 – April 26 2013………output “Fiscal 2012-2013”, etc…

Since I am not a VBA or SQL programmer, any help is more than welcome. Attached is a sample query containing the date field.
Thank you.

 

[apr pillai]

I have written a Function with the help of other built-in functions for these kind of calculations. But, I am fully aware that it is not the perfect solution. You may try it out after making changes at appropriate places in the Code. The link is given below:

Accounting Year/Week Calculations

Replace vbSunday with vbSaturday in the code and time-interval in DatePart() function with "yyyy" for year, "q" for quarter, "ww" for week etc.

You may duplicate the code with changes to the function name and DatePart() function parameters for different purposes.

good luck

 

[bimmer5]

Solved. Apr Pillai, truly appreciate your time and effort in helping me with this challenge! Thank you.